SQL Count question...

[annieko]

Hi guys,

I've got a query like this:

SELECT userID, invoiceID, count(distinct productID) AS product_count
From SALES

which will return results like:
userID invoiceID product_count
------ -------- -----------
0209 INV001 1
0207 INV006 1
0202 INV001 3

And I just want the records with product_count = 1, how can I do this?

I thought about putting the result in a temp table and then do my query again, but is there a quicker way of doing this?

[bubbisthedog]

For now I will assume that you're wanting to group by first the userID and then the invoiceID. If you want only to group by the userID, let me know, for this will change the query structure.

select userID, invoiceID, count(distinct productID) product_count
from SALES
group by userID, invoiceID
having count(distinct productID) = 1

By the way, 'having' is used to filter results after performing an aggregate function (min(), max(), count(), sum(), etc.) on them. If you were using 'where,' that keyword applies only to individual rows, which is why something like

where count(distinct productID) = 1

would not work. For what you're wanting, you first need to group results, and then filter them.