CODEWITHSUNDEEP
sql-servert-sqlcount
Peter Lange
Peter Lange

Reputation: 2876

TSQL Partitioning to COUNT() only subset of rows

I have a query that I am working on that, for every given month and year in a sales table, returns back a SUM() of the total items ordered as well as a count of the distinct number of accounts ordering items and a couple break down of SUm()s on various product types. See the query below for an example:

SELECT      
    YearReported, 
    MonthReported,
    COUNT(DISTINCT DiamondId) as Accounts,
    SUM(Quantity) as TotalUnitsOrdered,
    SUM(CASE P.ProductType WHEN 1 THEN Quantity ELSE 0 END) as MonthliesOrdered,
    SUM(CASE P.ProductType WHEN 1 THEN 0 ELSE Quantity END) as TPBOrdered
FROM       
    RetailOrders R WITH (NOLOCK)
LEFT JOIN  
    Products P WITH (NOLOCK) ON R.ProductId = P.ProductId
GROUP BY   
    YearReported, MonthReported

The problem I am facing now is that I also need to get the count of distinct accounts broken out based on another field in the dataset. For example:

SELECT      
    YearReported,
    MonthReported,
    COUNT(DISTINCT DiamondId) as Accounts, 
    SUM(Quantity) as TotalUnitsOrdered,
    SUM(CASE P.ProductType WHEN 1 THEN Quantity ELSE 0 END) as MonthliesOrdered,
    SUM(CASE P.ProductType WHEN 1 THEN 0 ELSE Quantity END) as TPBOrdered,
    SUM(CASE IsInitial WHEN 1 THEN Quantity ELSE 0 END) as InitialOrders,
    SUM(CASE IsInitial WHEN 0 THEN Quantity ELSE 0 END) as Reorders,
    COUNT(/*DISTINCT DiamndId WHERE IsInitial = 1 */) as InitialOrderAccounts 
FROM
    RetailOrders R WITH (NOLOCK)
LEFT JOIN  
    Products P WITH (NOLOCK) ON R.ProductId = P.ProductId
GROUP BY   
    YearReported, MonthReported

Obviously we would replace the commented out section in the last SUM with something that would not throw an error. I just added that for illustrative purposes.

I feel like this can be done using the Partition methods in SQL, but I have to admit that I just am not very good with them and cant figure out how to do this. And the MS documentation online for Partitions really just makes my head hurt after reading it last night.

EDIT: I mistakenly has the last aggregate function as sum and I meant for it to be a COUNT().

So to help clarify, COUNT(DISTINCT DiamondId) will give me back a count of all unique DiamondId values in the set but I also need to get a COUNT() of all Unique Diamond Id values in the set whose corresponding IsInitial flag is set to 1

Upvotes: 1

Views: 353

Answers (1)

shawnt00
shawnt00

Reputation: 17915

Just a matter of nulling the ones that don't qualify:

count(distinct
    case
        when IsInitial = 1 then DiamndId
        /* else null */
    end
)

Upvotes: 1

Related Questions