CODEWITHSUNDEEP
t-sqlaggregation
Jay
Jay

Reputation: 13

T-SQL : Avoid Subquery in favor of aggregation

I have used T-SQL for an awfully long time not to be able to do aggregation any better than I do, and this example will highlight that. I'm sure there are probably questions out there dealing with this but, not knowing any better about how to ask it, I also don't know any better how to search for it.

I have a view that aggregates some activity logging per team member Sample ActivityDetail ...

One of the things I need to do with this is get an aggregation (Activity Count by Member) for all Team Members for a given time period. I found it easy enough to get Per-Activity Totals in a dedicated row per Member/Activity using a concatenation hack found elsewhere on this site

SELECT DISTINCT
      a.Member
    , a.Activity
    , COUNT(CONCAT(a.Member,a.Activity)) AS ActivityCount

FROM v_Activity a 
WHERE    a.Activity_Date > DATEADD(DAY,-7,GETDATE()) 
GROUP BY a.Member,a.Activity
ORDER BY a.Member

Sample ActivityTotals But what we're really looking for here is a more consolidated representation of that. Now, I know I can get there with subqueries doing COUNT(*) for each ActivityType like so

SELECT DISTINCT
      a.Member
    , (SELECT COUNT(*) FROM v_UpdateActivity WHERE Member = a.Member AND Activity = 'Create' AND Activity_Date > DATEADD(DAY,-7,GETDATE())) As Created
    , (SELECT COUNT(*) FROM v_UpdateActivity WHERE Member = a.Member AND Activity = 'Update' AND Activity_Date > DATEADD(DAY,-7,GETDATE())) As Updated
    , (SELECT COUNT(*) FROM v_UpdateActivity WHERE Member = a.Member AND Activity = 'Document' AND Activity_Date > DATEADD(DAY,-7,GETDATE())) As Documented
    , (SELECT COUNT(*) FROM v_UpdateActivity WHERE Member = a.Member AND Activity = 'Deactivate' AND Activity_Date > DATEADD(DAY,-7,GETDATE())) As Deactivated
    , (SELECT COUNT(*) FROM v_UpdateActivity WHERE Member = a.Member AND Activity = 'Reactivate' AND Activity_Date > DATEADD(DAY,-7,GETDATE())) As Reactivated

FROM v_cw_ConfigUpdateActivity a 
WHERE    a.Activity_Date > DATEADD(DAY,-7,GETDATE()) 
ORDER BY a.Member

And this does a decent job of getting me the output I want (including zeros for no activity). Desired Output

But (a) this is inefficient - it already doesn't perform well and (b) this just screams aggregation but I am apparently too thick to think of the right way to construct this to get what I'm looking for. Any help?

Upvotes: 1

Views: 30

Answers (1)

JNevill
JNevill

Reputation: 50034

Instead of subqueries you can use CASE statements and a GROUP BY. Alternatively you could do this with a PIVOT.

SELECT 
      a.Member
      SUM(CASE WHEM Activity = 'Create' THEN 1 ELSE 0 END) as Created,
      SUM(CASE WHEM Activity = 'Updated' THEN 1 ELSE 0 END) as Updated,
      SUM(CASE WHEM Activity = 'Documented' THEN 1 ELSE 0 END) as Documented,
      SUM(CASE WHEM Activity = 'Deactivated' THEN 1 ELSE 0 END) as Deactivated,
      SUM(CASE WHEM Activity = 'Reactivated' THEN 1 ELSE 0 END) as Reactivated    
FROM v_cw_ConfigUpdateActivity a 
WHERE    a.Activity_Date > DATEADD(DAY,-7,GETDATE()) 
GROUP BY a.Member
ORDER BY a.Member;

My tsql skills may be showing their rust here, but this is an attempt at a PIVOT version of this:

SELECT
    Member,
    Created,
    Updated,
    Documented,
    Deactivated,
    Reactivated
FROM (SELECT Member, Activity FROM v_cw_ConfigUpdateActivity) as SourceTable
PIVOT (COUNT(Activity) FOR Member IN (Created, Updated, Documented, Deactivated, Reactivated) as PivotTable

Upvotes: 2

Related Questions