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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
deshg said:
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?
Click to expand...


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.
 
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
Click to expand...
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.
 
Back
Top