Have 2 tables: PERSONS: CREATE TABLE [PERSONS]([ID] INTEGER PRIMARY KEY, [Place] TEXT)
ATTENDED: CREATE TABLE [ATTENDED]([ID] INTEGER) Sample date like this: PERSONS: ID Place ----------- 1 A 2 A 3 B 4 A 5 A 6 A 7 B 8 B 9 A 10 A ATTENDED: ID --------- 1 5 6 1 1 8 9 5 1 5 8 1 6 8 9 9 1 5 6 1 Now I would like to show the counts of persons that not attended, grouped by place and in a third column the counts of all persons, again grouped by place. I can do it differently in 2 columns with a union: SELECT P.PLACE, COUNT(P.ID) AS P_COUNT FROM PERSONS P LEFT JOIN ATTENDED A ON(P.ID = A.ID) WHERE A.ID IS NULL GROUP BY P.PLACE UNION ALL SELECT PLACE, COUNT(ID) AS P_COUNT FROM PERSONS GROUP BY PLACE But I would like the result to be in 3 columns, so result in this case would be: Place Not_Attended All ------------------------------- A 3 7 B 2 3 Probably simple, but I can't work it out and thanks for any assistance. RBS _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
