Hi, I can query for number of records per Group, e.g. this works:
SELECT count(distinct a.RecordID),g.groupname FROM Record as a left outer join RECCAT as b on b.recordID = a.recordID left outer join cat as c on c.catid = b.catid left outer join [Group] as g on g.groupID = c.groupID where g.groupname <> 'Other' group by g.groupname
but I need to add to this query, so that I can also count each lookupvalue associated with records for each group.
So, for each groupname I want to count the records associated with each value (RELVAL.lookupvalue)
my tables: GROUP(GroupID, GroupName) CAT(CatID,CatName,GroupID) RECCAT(CatID,RecordID) RECORD(RecordID, Creator, Date) RECREL(RecordID,LookupID) RELVAL(LookupID, LookupValue, ValueID)
e.g. Group A can have 10records associated with UK (where relval.lookupvalue = 'UK'), and 5 where relval.lookupvalue = 'US' etc.. each lookupvalue specified in the select count statement should be displayed as the column name, something like this:
GroupName UK US ASIA AFRIKA A 10 5 6 0
my query below doesn't return correct results:
SELECT distinct g.groupname, (select count(distinct a.recordID) from RECCAT as x inner join RECREL as y on y.recordID = x.recordID inner join RELVAL as z on z.lookupID = y.lookupD where x.recordid = a.recordid and z.valueID=43 and z.lookupvalue = 'United Kingdom)as UK FROM RECORD as a inner join RECCAT as b on b.recordID = a.recordID inner join CAT as c on c.catid = b.catid inner join [Group] as g on g.groupID = c.groupID
As I am only interested in the values associated with records within each group where the valueID = 43, I am not sure if I need a select count statement for each value or is there a way to write one which will display all values as columnnames where valueid=43 and count for each group?
Any help is much appreciated.