I have a php/mySQL database recording client visits to an advice centre. The tables involved are Clients Matters and Ethnicity
Clients has fields client, clientname, ethnicid
Ethnicity has fields ethnic and ethnicity
Matters has fields mattered, client, clientname
I want to produce a table which gives totals for the number of unique clients seen. Thus, if someone comes for any number of matters they only count once. Also I need to break the totals down by ethnicity.
In a separate set of tables (to count the advice given) I had a query of which part is:
SELECT COUNT(*), SUM(CASE WHEN ethnicid='5' THEN 1 ELSE 0 END) AS wh, SUM(CASE WHEN ethnicid='6' THEN 1 ELSE 0 END) AS EN ..etc
If I try the same thing in my new table it wont work as it includes duplicate clients. How can I modify my query to remove duplicates?