Alan Alan - 4 months ago 20
SQL Question

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.

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