question

keentolearn avatar image
keentolearn asked

I need help with this please! NEED TO FIX BEFORE MONDAY :( Counting distinct values in a multivalue field for each group they belong to..

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
B ..
C ..
D ..

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.

many thanks

sql-server-2005aggregates
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered 
            
SELECT [GroupName]            
,ISNULL([US],0)[US]            
,ISNULL([UK],0)[UK]            
,ISNULL([ASIA],0)[ASIA]            
,ISNULL([AFRIKA],0)[AFRIKA]            
FROM(            
    SELECT g.GroupName, rv.LookupValue, COUNT(DISTINCT rr.LookupID) [Records]            
    FROM RECORD r JOIN RECCAT rc ON (rc.RecordID = r.RecordID)            
    JOIN CAT c ON (c.CatID = rc.CatID)             
    JOIN [GROUP] g ON (g.GroupID = c.GroupID)             
    JOIN RECREL rr ON (rr.RecordID = R.RecordID)            
    JOIN RELVAL rv ON (rv.LookupID = rr.LookupID)            
    WHERE g.GroupName <> 'Other'             
    AND rv.ValueID = 43            
GROUP BY g.GroupName, r.RecordID, rv.LookupValue            
)src            
PIVOT(            
SUM([Records])            
FOR LookupValue IN ([US], [UK], [ASIA], [AFRIKA]))pvt            
ORDER BY GroupName
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.