Include "0" results in COUNT(*) aggregate

Question

Good morning, I've searched in the forum one doubt that I have but the results that I've seen didn't give me a solution.

I have two tables.

CARS:

Id    Model
1     Seat
2     Audi
3     Mercedes
4     Ford

BREAKDOWNS:

IdBd   Description         Date         Price      IdCar
1      Engine              01/01/2020   500 €      3
2      Battery             05/01/2020   0 €        1
3      Wheel's change      10/02/2020   110,25 €   4
4      Electronic system   15/03/2020   100 €      2
5      Brake failure       20/05/2020   0 €        4
6      Engine              25/05/2020   400 €      1

I wanna make a query that shows the number of breakdowns by month with 0€ of cost.

I have this query:

SELECT Year(breakdowns.[Date]) AS YEAR, StrConv(MonthName(Month(breakdowns.[Date])),3) AS MONTH, Count(*) AS [BREAKDOWNS]
FROM cars LEFT JOIN breakdowns ON (cars.Id = breakdowns.IdCar AND breakdowns.[Price]=0)
GROUP BY breakdowns.[Price], Year(breakdowns.[Date]), Month(breakdowns.[Date]), MonthName(Month(breakdowns.[Date]))
HAVING ((Year([breakdowns].[Date]))=[Insert a year:])
ORDER BY Year(breakdowns.[Date]), Month(breakdowns.[Date]);

And the result is (if I put year '2020'):

YEAR   MONTH       BREAKDOWNS
2020   January     1
2020   May         1

And I want:

YEAR   MONTH       BREAKDOWNS
2020   January     1
2020   February    0
2020   March       0
2020   May         1

Thanks!

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jul 22 '20 at 10:45

score 0 · Answer 1 · answered Jul 22 '20 at 09:42

The HAVING condition should be in WHERE (otherwise it changes the Outer to an Inner join). But as long as you don't use columns from cars there's no need to join it.

To get rows for months without a zero price you should switch to conditional aggregation (Access doesn't support Standard SQL CASE, but IIF?).

SELECT Year(breakdowns.[Date]) AS YEAR,
   StrConv(MonthName(Month(breakdowns.[Date])),3) AS MONTH,
   SUM(CASE WHEN breakdowns.[Price]=0 THEN 1 ELSE 0 END) AS [BREAKDOWNS]
FROM breakdowns
JOIN cars
  ON (cars.Id = breakdowns.IdCar)
WHERE ((Year([breakdowns].[Date]))=[Insert a year:])
GROUP BY breakdowns.[Price], Year(breakdowns.[Date]), Month(breakdowns.[Date]), MonthName(Month(breakdowns.[Date]))
ORDER BY Year(breakdowns.[Date]), Month(breakdowns.[Date]

Syntax error with your query and with "SUM(IIf(breakdowns.[Price]=0;1;0) AS [BREAKDOWNS]" — adrilash, Jul 22 '20 at 09:57
2nd closing parens is missing: `SUM(IIf(breakdowns.[Price]=0;1;0)) AS [BREAKDOWNS]` — dnoeth, Jul 22 '20 at 10:50

Include "0" results in COUNT(*) aggregate

1 Answers1