Showing records with 0 total in a group by/sum query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deshg - 17 Mar 2007 22:06 GMT

Hey everyone,

I have a query displaying total customers within specific sectors using the
groupby and sum arguments. The only issue being it doesn't display sectors
with 0 customers at all (i would like it to list them with 0 total). I just
wondered if there was a way of doing this or whether it will only ever show
results with positive counts?

Thanks so much,

Dave

Reply to this Message

Marshall Barton - 17 Mar 2007 23:20 GMT

>I have a query displaying total customers within specific sectors using the
>groupby and sum arguments. The only issue being it doesn't display sectors
>with 0 customers at all (i would like it to list them with 0 total). I just
>wondered if there was a way of doing this or whether it will only ever show
>results with positive counts?

You need a table with all sectors. Then your query can use
an outer join to the customers table.

If you need more help than that, please post a Copy/Paste of
your query's SQL view.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Michael Gramelspacher - 18 Mar 2007 01:25 GMT

In article <CDAABEE1-64F8-4745-BF63-D2D20CABF780
@microsoft.com>, deshg@discussions.microsoft.com says...

> Hey everyone,
>
[quoted text clipped - 7 lines]
>
> Dave

If you have a COUNT(customer_id) in your query, then COUNT
omits nulls. So, you may need something like:

SELECT sector_nbr, (SELECT COUNT (*) FROM Customers AS a
WHERE a.sector_nbr = Customers.sector_nbr) AS [Customer Count]
FROM Customers;

or you can provide SQL as Marshall mentioned and you surely
will be provided a solution.

Reply to this Message