Multiple COUNT(DISTINCT) from CTE

Question

This is for SQL Server 2012: a subset of the data in my CTE looks like this:

Employee | OrderID | OrderType
---------+---------+----------
Kala     | 321111  | 953
Paul     | 321222  | 1026
Don      | 321333  | 1026
Don      | 321333  | 953
Kala     | 321444  | 953

I'd like the following result:

Employee | 953_Order_Count | 1026_Order_Count
---------+-----------------+-----------------
Kala     |        2        |    0
Don      |        1        |    1
Paul     |        0        |    1

To validate that I want is possible in my mind, when I run:

SELECT 
    Employee,
    OrderType,
    COUNT(DISTINCT OrderID) AS 'Count'
FROM
    CTE
GROUP BY 
    employee, ordertype

The following result is returned:

Employee | OrderType | Count
---------+-----------+------
Kala     | 953       | 1
Paul     | 1026      | 1
Don      | 1026      | 1
Don      | 953       | 1

Close, but not close enough. So I run:

SELECT 
    Employee,
    COUNT(DISTINCT OrderID) AS 'Total_Orders',
    COUNT(DISTINCT (CASE WHEN OrderType = 1026 THEN OrderID END)) AS '1026_Order_Count',
    COUNT(DISTINCT(CASE WHEN OrderType = 953 THEN OrderID END)) AS '953_Order_Count'
FROM
    CTE
GROUP BY 
    Employee

The result is an accurate first "count," but the rest return 0. If this were not a CTE, I'd use a recursive statement.

Any help is appreciated!

To clarify a bit, the OrderID may have more than OrderType – Brian Spittle Oct 04 '18 at 15:40 — Brian Spittle, Oct 04 '18 at 15:40

Tim Biegeleisen · Accepted Answer · 2018-10-04T04:43:33.380

1

Just use conditional aggregation:

SELECT
    Employee,
    COUNT(CASE WHEN OrderType = 953  THEN 1 END) AS [953_Order_Count],
    COUNT(CASE WHEN OrderType = 1026 THEN 1 END) AS [1026_Order_Count]
FROM CTE
GROUP BY
    Employee;

Demo

The 953 count, for example, works above by counting 1 when the order type is 953 and NULL (the implicit ELSE value) when the order type is not 953. COUNT ignores NULL by default, so it only counts the 953 orders.

edited Oct 04 '18 at 04:43

answered Oct 04 '18 at 04:38

Tim Biegeleisen

502,043
27
286
360

Thanks, @tim-biegeleisen. This wasn't working because I had a typo in my case statement - even after staring at it for an hour. – Brian Spittle Oct 04 '18 at 16:01

score 0 · Answer 2 · answered Oct 04 '18 at 12:15

Tim's answer looks fine. You could also use a PIVOT:

; with cte (Employee, OrderID, OrderType)
as
(
    select 'Kala', 321111, 953
union select 'Paul', 321222, 1026
union select 'Don', 321333, 1026
union select 'Don', 321333, 953
union select 'Kala', 321444, 953
)
select Employee, [953] as [953_Order_Count],[1026] as [1026_Order_Count]
from 
    (
        select Employee, OrderType from cte ) as sourceData
pivot
(
    count(OrderType)
    for OrderType
    in ([953],[1026])
) as myPivot

If you want to have dynamic columns based on the set of available values in the OrderType column, you can build the query dynamically. See @Taryn's answer to Understanding PIVOT function in T-SQL for an example.

This also works - and will be my foray into pivots. Thank you! — Brian Spittle, Oct 04 '18 at 16:02

Multiple COUNT(DISTINCT) from CTE

2 Answers2

Demo