CODEWITHSUNDEEP
sql
Assaf
Assaf

Reputation: 1124

Count Number of Items Per Client and List these Items

I have a relation in my assignment:

File (fid, fname, description, status, cname, lname)

The whole DB is a law firm and fid represent file ID, cname - client name, lname - lawyer name. The rest is irrelevant at the moment.

What I want to do is:

Find a client who has only 1 file and return the file and the client name.

I have no problem finding which client it is:

select file.cname, count(fid), 
from file
group by cname
having count(fid)=1

However if I add fid to this query I get a bad result.

What can I do?

Thanks.

Upvotes: 0

Views: 42

Answers (3)

kjmerf
kjmerf

Reputation: 4345

Alan if you want to select clients with count =2, 3, etc. you can use the query below and just adjust the number in the last line:

SELECT file.cname, file.fname
FROM file
WHERE file.cname IN
(SELECT file.cname
FROM file
GROUP BY file.cname
HAVING COUNT(fid) = 2);

Upvotes: 1

Robert Columbia
Robert Columbia

Reputation: 6418

select file.cname, count(fid), max(fid) 
from file
group by cname
having count(fid)=1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270643

If there is only one value, then you can use min() or max() to get it:

select f.cname, min(f.fid) as fid
from file f
group by f.cname
having count(f.fid) = 1;

You don't need to specify the count() in the select in order to use it in the having clause (in most databases . . . there are exceptions, but I doubt you are using Hive).

Upvotes: 2

Related Questions