CODEWITHSUNDEEP
sqlsql-serverpivot
Lincecum
Lincecum

Reputation: 825

Grouping multiple counts with each row representing one ID

I have a table that logs activity. It has an int activity column that indicates what type of activity it was and a corresponding admin_id that indicates which admin performed the action. Each row in the table represents one action, so for example, an administrator would cancel a user's account, and a row with that admin's id and activity=[int representing cancel] would be created. I need a way to a select a result which groups each admin's activity such that the row corresponding to the admin would have the # of each activity

The result would look like

admin_id | numActivated | numCanceled | numRenewed
1          1              1             2
2          1              3             0

With numActivated, numCanceled, and numRenewed all coming from the count for each activity.

This is what the original table would have looked like:

admin_id | activity ...
1          2
1          1
1          3
1          3
2          2
2          2
2          2
2          1

(activity 1: activate, 2: cancel, 3: renew)

Thanks

Upvotes: 2

Views: 120

Answers (2)

eyjo
eyjo

Reputation: 1210

This should do it:

SELECT
    T.admin_id,
    sum(T.activate) AS numActivated,
    sum(T.canceled) AS numCanceled,
    sum(T.renew) AS numRenewed
FROM (
    SELECT
        admin_id,
        CASE activity WHEN 1 THEN 1 ELSE 0 END AS activate,
        CASE activity WHEN 2 THEN 1 ELSE 0 END AS canceled,
        CASE activity WHEN 3 THEN 1 ELSE 0 END AS renew
    FROM Table
) AS T
GROUP BY T.admin_id

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

This should do what you're asking for:

select admin_id,
       sum(case when activity = 1 then 1 else 0 end) as numActivated,
       sum(case when activity = 2 then 1 else 0 end) as numCanceled,
       sum(case when activity = 3 then 1 else 0 end) as numRenewed
    from YourTable
    group by admin_id

Upvotes: 3

Related Questions