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

[Guest]

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

 

[Marshall Barton]

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.

 

[Michael Gramelspacher]

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

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.