Alan Alan - 2 years ago 55
SQL Question

Group By with Count Function Unclear

I have in my DB the following relation:


It describes the subscribers to magazines ("title"), with the primary key "cid" - customer id. The other relations here are not relevant.

This is the code I run, the find all the customers (cid) that have a subscription to only one magazine:

select count(cid), cid
from subscribes as r1
group by cid
having count(cid)=1;

The question

How do I find that one magazine, that these customers are subscribed to?

I tried just adding the "title" column to the code, and it gave me all the titles, with a $1$ on the left column of count.



Answer Source
SELECT title, 
FROM   subscribes 
WHERE  cid IN (SELECT cid 
               FROM   subscribes 
               GROUP  BY cid 
               HAVING Count(cid) = 1); 

Use this query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download