Alan Alan - 1 year ago 72
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?


Answer Source
select file.cname, count(fid), max(fid) 
from file
group by cname
having count(fid)=1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download