CODEWITHSUNDEEP
sqlexcelodbccase-when
Jack Williams
Jack Williams

Reputation: 141

Count distinct case when SQL

Wondering if I could have a bit of help. I'm still new to SQL and I am learning by the day trying to do something a bit more complicated each day.

I have a dataset which has duplicated order numbers in it. From this I am wanting to do two things: firstly I am looking at doing a count of the number of unique order numbers and then secondly the sum of all the order numbers.

I have managed to achieve part two of this but I am having a bit of difficulty executing the Count of distinct order numbers.

Please see below my query I know my joins are not correct right now as I will be working on them later. I am working from an ODBC progress v10 and using Microsoft query manager to extract and then hand writing the code in note pad then transferring back to Excel as currently my company can't get me SSMS.

SELECT 
    Company_0.CoaCompanyName,
    SopOrder_0.SooOrderDate,
    COUNT(DISTINCT CASE WHEN SopOrder_0.SooOrderNumber = SopOrder_0.SooOrderNumber THEN 1 ELSE 0 END) AS 'Orders',
    SUM(CASE WHEN SopOrder_0.SooOrderNumber IS NOT NULL THEN 1 ELSE 0 END) AS 'Order Items',
    SopOrderItem_0.SoiValue,
    SopOrder_0.SooParentOrderReference
FROM 
    SBS.PUB.Company Company_0,
    SBS.PUB.SopOrder SopOrder_0,
    SBS.PUB.SopOrderItem SopOrderItem_0
WHERE 
    SopOrder_0.SopOrderID = SopOrderItem_0.SopOrderID 
    AND Company_0.CompanyID = SopOrder_0.CompanyID
    AND SopOrder_0.SooOrderDate > '2019-01-01'

Current data looks like

Company Name           Order Numbers    
-------------------------------------
CompanyA                 Orderno1
CompanyA                 Orderno1
CompanyB                 Orderno2
CompanyB                 Orderno3
CompanyB                 Orderno3
CompanyB                 Orderno3

How I want it to look:

Company Name           Orders         Order Items
-------------------------------------------------
CompanyA                 1                2
CompanyB                 2                4

Upvotes: 1

Views: 635

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You could use count (distinct..), count(*) and group by 

    SELECT 
        Company_0.CoaCompanyName
        , Count(DISTINCT SopOrder_0.SooOrderNumber ) AS Orders
        , count(*) AS `Order Item`
    FROM SBS.PUB.Company Company_0
    INNER JOIN SBS.PUB.SopOrder SopOrder_0 ON Company_0.CompanyID = SopOrder_0.CompanyID
    INNER JOIN SBS.PUB.SopOrderItem SopOrderItem_0 ON SopOrder_0.SopOrderID = SopOrderItem_0.SopOrderID 
    WHERE SopOrder_0.SooOrderDate > '2019-01-01'
    GROUP BY Company_0.CoaCompanyName

For readability, you should use explicit join syntax and avoid old implicit join syntax based on where condition.

For composite column name, you could use back-ticks ("`").

Upvotes: 1

Related Questions