I have two tables, City and Vehicle. Now I want to merge the two tables into one table. Then count each type of vehicle based on city. Then merge them in to one table. Could someone help me?
City:
| City_Id | City_Name |
|---|---|
| 1 | New York |
| 2 | Washington |
Vehicle:
| Vehicle_Id | Vehicle_Type | City_Id |
|---|---|---|
| 1 | Electric | 2 |
| 2 | Fuel | 1 |
| 3 | Fuel | 2 |
| 4 | Fuel | 1 |
| 5 | Electric | 1 |
I want to achieve this. Merge tables City and Vehicle into Vehicle_Count, based on column ‘City Name’. Table Vehicle_Count should include City Name, different vehicle types as different columns, total number of vehicles of different vehicle type group by city. If there is no data on each of other table, the value set 0. The column ‘Total’ is the count of vehicles of all vehicle type.
Vehicle_Count:
| City_Name | Electric | Fuel | Total |
|---|---|---|---|
| New York | 1 | 2 | 3 |
| Washington | 1 | 1 | 2 |
I have tried this SQL
SELECT c.City_Name,COUNT(*) AS Electric FROM Vehicle v
LEFT JOIN City c ON c.City_Id = v.City_Id
WHERE v.Vehicle_Type = 'Electric'
GROUP BY v.City_Id
Only got this
| City_Name | Electric |
|---|---|
| New York | 1 |
| Washington | 1 |
>Solution :
you can try this
SELECT City_Name , Electric, Fuel, total1+total2 AS Total FROM (
SELECT sc.City_Name, COUNT(*) AS Electric, 0 AS Fuel FROM Vehicle si
LEFT JOIN City sc ON sc.City_Id = si.City_Id
WHERE si.Vehicle_Type = "Electric"
GROUP BY si.City_Id
UNION ALL
SELECT sc.City_Name,0 AS Electric, COUNT(*) AS Fuel FROM Vehicle si
LEFT JOIN City sc ON sc.City_Id = si.City_Id
WHERE si.Vehicle_Type = "Fuel"
GROUP BY si.City_Id
) A
GROUP BY City_Name