T-SQL Count in single select

Question

I have a table called Catalog. It has multiple columns, for example CreatedByID and ModifiedByID that contain user IDs. I would like to get count of how many documents a user has created and edited.

I've spent hours looking for alternatives for this but couldn't find anything that works. This works and I get correct results but is there a simpler way to do this?

SELECT l.UserName
 , US.Used
 , US.Created
 , US.MostRecent
 , US.Edited
 FROM [Users] US
 JOIN (SELECT UserName
 , COUNT(UserName) AS Used
 , MAX([TimeStart]) AS MostRecent 
 FROM [Log] 
 WHERE ReportAction = 1 
 GROUP BY UserName) AS l
 ON (Us.UserName = l.UserName)
 JOIN (SELECT CreatedByID
 , COUNT(CreatedByID) AS Created 
 FROM [Catalog] 
 GROUP BY CreatedByID) AS Cat
 ON (Us.UserID = Cat.CreatedByID)
 JOIN (SELECT ModifiedByID
 , COUNT(ModifiedByID) AS Edited 
 FROM [Catalog] 
 GROUP BY ModifiedByID) AS Cat2
 ON (Us.UserID = Cat2.ModifiedByID)

I would like to get both COUNT(CreatedByID) and COUNT(ModifiedByID) in a single select. For example I can get how many documents user has created with this:

SELECT CreatedByID, COUNT(CreatedByID) AS Created
FROM [Catalog]
GROUP BY CreatedByID

And how many documents user has edited with this:

SELECT ModifiedByID, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY ModifiedByID

But how to do those on a single select? I've tried something like this, but the results are not correct any more for ModifiedByID.

 SELECT CreatedByID, COUNT(CreatedByID) AS Created, COUNT(ModifiedByID) AS Modified
FROM [Catalog]
GROUP BY CreatedByID

• ypercubeᵀᴹ: The query seems fine to me. I don't think you'll gain anything if you manage to squeeze the 3 subqueries to 2, only lose readability.
• Scott Hodgin - Retired: Hmm - what about users that created, but did not modify or modified but did not create? Sounds like left join territory to me

Answer 1

What you are trying to do can be resolved using two operations: an unpivot followed by a pivot.

The table that is most principal to the query you are looking for, Catalog, is document-centric. It stores data in this form:

What | Created by Whom | Edited by Whom

You, however, want a user-centric report. So, first of all, you need to transform the original set to a user-centric one:

Who | Did What | To What

The To What part is not strictly necessary in this case, perhaps, but I leave it for a better illustration of how the data are rearranged in the course of the sought-for transformation. And the transformation itself is called unpivot. Assuming the t-sql tag on your question means SQL Server rather than Sybase, an unpivot in your case can be implemented like this:

SELECT
 u.UserID,
 Action = LEFT(u.Action, LEN(u.Action) - 4)
FROM
 dbo.Catalog AS c
 UNPIVOT
 (
 UserID
 FOR Action IN (CreatedByID, ModifiedByID)
 ) AS u
;

The LEFT() trick simply cuts off the ByID bit from each name, leaving just the verb.

Another way would be like this:

SELECT
 u.UserID,
 u.Action
FROM
 dbo.Catalog AS c
 CROSS APPLY
 (
 VALUES
 (CreatedByID, 'Created'),
 (ModifiedByID, 'Modified')
 ) AS u (UserID, Action)
WHERE
 u.UserID IS NOT NULL
;

I am assuming that a document that has not been modified will have a null in ModifiedByID. Filtering out the Modified entries with a null user ID is what the WHERE clause in the above query for.

Now you could simply group the results by UserID, Action and count the rows in each group. That would give you the output values in this form:

UserID Action Count
------ -------- -----
100 Created 10
100 Modified 15
110 Created 8
... ... ...

But as you want Created and Modified as separate columns:

UserID Created Modified
------ ------- --------
100 10 15
... ... ...

you will need to group the results with a pivot. One way to do so is to use the native PIVOT syntax:

SELECT
 UserID,
 Created,
 Modified
FROM
 (
 ... /* the unpivot query (either option) */
 ) AS s
 PIVOT
 (
 COUNT(Action)
 FOR Action IN (Created, Modified)
 ) AS p
;

You could also achieve the same using the older syntax called conditional aggregation:

SELECT
 UserID,
 Created = COUNT(CASE Action WHEN 'Created' THEN 1 END),
 Modified = COUNT(CASE Action WHEN 'Modified' THEN 1 END)
FROM
 (
 ... /* the unpivot query (either option) */
 ) AS s
GROUP BY
 UserID
;

In the latter case the derived table can be avoided. Instead you would just need to use the FROM clause and, if necessary, the WHERE clause from either unpivoting method. That is, the final conditional aggregation query would look either like this:

SELECT
 u.UserID,
 Created = COUNT(CASE u.Action WHEN 'Created' THEN 1 END),
 Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
 dbo.Catalog AS c
 UNPIVOT
 (
 UserID
 FOR Action IN (CreatedByID, ModifiedByID)
 ) AS u
GROUP BY
 u.UserID
;

or like this:

SELECT
 u.UserID,
 Created = COUNT(CASE u.Action WHEN 'Created' THEN 1 END),
 Modified = COUNT(CASE u.Action WHEN 'Modified' THEN 1 END)
FROM
 dbo.Catalog AS c
 CROSS APPLY
 (
 VALUES
 (CreatedByID, 'Created'),
 (ModifiedByID, 'Modified')
 ) AS u (UserID, Action)
WHERE
 u.UserID IS NOT NULL
GROUP BY
 u.UserID
;

You can use the final query as a derived table to join the results to the Users table to further connect them with the results from the Log table – in much the same way as you are doing now.

• Gary: Fantastic answer.

Answer 2

I personally like this approach:

;with U (u,name) 
AS
(
 select u.UserID, u.UserName from users as u
)
,C (createdbyid,cnt) as (select createdbyid, count(createdbyid) from catalog group by createdbyid)
,M (ModifiedbyID,cnt) as (select ModifiedbyID, count(ModifiedbyID) from catalog group by ModifiedbyID)
select U.u, u.name, isnull(C.cnt,0 ) as CreateCount, isnull(M.cnt,0) as ModCount from U left join C on U.u = C.createdbyid left join M on U.u = M.ModifiedbyID

It does not exactly address your question for one simple (it is ONE but it is not as simple as you would like it to be) query as you tried to produce doing

Select ... Count(a), Count(b) ... Group By X, Y ...

but this will never work since you can only COUNT the number of rows of the result grouped by X,Y... and this will be the same number of rows for no matter how many COUNTS you do...

But after all my query is slighter and more readable than yours, that's why I would give this option a try.

• ypercubeᵀᴹ: I wouldn't call this readable. I mean I like the CTEs but the formatting is awful ;)
• Magier: True. I created it quick n dirty :)