By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

count feature (group by)

P: n/a
If you have a select with 2 attributes where you group by one attribute
and do a count() for the second attribute, if the count() is 0 then
that row is never displayed. How would you instead see a line item
where you list the attribute with 0 as the count.

Aug 16 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"sammy" <va******@cs.stanford.eduskrev i en meddelelse
news:11*********************@75g2000cwc.googlegrou ps.com...
If you have a select with 2 attributes where you group by one attribute
and do a count() for the second attribute, if the count() is 0 then
that row is never displayed. How would you instead see a line item
where you list the attribute with 0 as the count.
I'm not sure I understand what you're asking. So this is based on some
assumptions about the nature of your problem:

If you have a table with two columns, ColA and ColB, with these data:

1, 4
2, 5
3, 1
1, 8
5, 1

and you have a query like this:

SELECT ColA, COUNT(ColB)
GROUP BY ColA

the result would be

1, 2
2, 1
3, 1
5, 1

It's my understanding (wich may be wrong) that
(if the possible values of ColA is 1-5)
you would have liked the result to be

1, 2
2, 1
3, 1
4, 0
5, 1

However, this is based on the knowledge of the possible values of ColA. SQL
has no way of knowing about these values if they are not present in the
table, hence the query will count only the rows whose group values are
present in ColA of the table.

If you really want to return a count for each possible value, regardless if
they are present in the table, you need to create a table with the possible
values (one column only):

Then you could modify your query a little:

SELECT Allval.ColA, COUNT(ColB)
FROM Allval
LEFT JOIN Table ON Allval.ColA = Table.ColA
GROUP BY Allval.ColA

Aug 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.