I’m trying to generate a cars sell report using SQL.

The report must be grouped and ordered by Customer and Period (format M/yyyy)

The report result table must have the following columns:

• Customer: The customer who bought the vehicle.
• Period: the date of sale.
• Cars quantity: The quantity of vehicles sold for that customer in that period, which type is Car.
• Cars total price: The sum of the prices of vehicles (with Car type) sold to that customer in that period.
• Trucks quantity: the quantity of vehicles sold for that customer in that period, which type is Truck.
• Trucks total price: the sum of the prices of vehicles (with Truck type) sold to that customer in that period.

You can find the SQL dump file to generate the database and data here:

Here’s the query I’m using in attempt to generate the report:

SELECT c.Name_Customer AS 'Customer', FORMAT(v.Sell_Date, 'M/yyyy') AS 'Period',
CASE 
    WHEN t.Name_Vehicle_Type = 'Car' THEN (COUNT(v.ID_Vehicle)) ELSE NULL END AS 'Cars quantity',
CASE
    WHEN t.Name_Vehicle_Type = 'Car' THEN SUM(v.Sell_Price) ELSE NULL END AS 'Cars total price',
CASE 
    WHEN t.Name_Vehicle_Type = 'Truck' THEN (COUNT(v.ID_Vehicle)) ELSE NULL END AS 'Trucks quantity',
CASE
    WHEN t.Name_Vehicle_Type = 'Truck' THEN SUM(v.Sell_Price) ELSE NULL END AS 'Trucks total price'
FROM Customer AS c, Vehicle AS v, Vehicle_Type AS t
WHERE c.ID_Customer = v.ID_Customer and v.ID_Vehicle_Type = t.ID_Vehicle_Type
GROUP BY v.ID_Customer, FORMAT(v.Sell_Date, 'M/yyyy'), c.Name_Customer, v.ID_Customer, t.Name_Vehicle_Type
HAVING (t.Name_Vehicle_Type = 'Car' or t.Name_Vehicle_Type = 'Truck')
ORDER BY c.Name_Customer, 'Period'

Here’s what I get when running the query:

But I was expecting to receive:

Can you see the problem? It’s generating an extra line with NULL values in certain cells. I wish to mix the two lines grouping by the customer and period as shown above.