ms access - query null row

Question

Is it possible a query with Group By to show null rows?

let's say that my table has [PlaceID] and [Times].

PlaceID I Times
--------I-------
1       I   2
3       I   1
1       I   1
3       I   2
3       I   4
1       I   2

If I make the following SQL, [PlaceID] will not be visible because there is no data.

SELECT PlaceID, Sum(Times) As SumTimes
FROM tblOrder
GROUP BY PlaceID;


PlaceID I SumTimes
--------I-------
1       I   5
3       I   7

Is it possible to force it and have this output

PlaceID I SumTimes
--------I-------
1       I   5
2       I   0
3       I   7

Gordon Linoff · Accepted Answer · 2018-07-07 15:30:08Z

2

You need a list of places . . . which I would guess is in the places table.

Then:

select p.placeId, nz(sum(times), 0)
from places as p left join
     tblOrder as o
     on p.placeId = o.placeId
group by p.placeId;

If there are more than three places, you can add where p.placeId in (1, 2, 3).

answered Jul 7, 2018 at 15:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

You are correct there is tblPlace with PlaceID and Place.

And there are more than 3. Since a user can add a place, the sql will have to see all PlaceID