count feature (group by)

[sammy]

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.

[Kaj Julius]

"sammy" <va******@cs.st anford.eduskrev i en meddelelse
news:11******** *************@7 5g2000cwc.googl egroups.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