How can I get sum and count of all sub-counts and sub-sums

Transact-SQL
1

Basically, I have a query like this:

SELECT dc.email,  SUM(bft.amount_usd) AS TOTALSPENT,
SUM(CASE WHEN doct.Product_name = 'Books' THEN bft.amount_usd ELSE 0 END) TOTALSPENT_BOOKS,
SUM(CASE WHEN doct.Product_name = 'Shoes' THEN bft.amount_usd ELSE 0 END) TOTALSPENT_SHOES,
COUNT(DISTINCT CASE WHEN doct.CATEGORY_A = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_A_count,
COUNT(DISTINCT CASE WHEN doct.CATEGORY_B = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_B_count,
COUNT(DISTINCT CASE WHEN doct.CATEGORY_C = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_C_count,
COUNT(DISTINCT CASE WHEN doct.CATEGORY_D = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_D_count
FROM ProductTypeTable doct
    INNER JOIN customerTable dc ON dc.customerID = doct.customerID
    INNER JOIN transactionsTable bft ON bft.transactionID = doct.transactionID
GROUP BY dc.email

CATEGORY_A - is a Boolean field (1/0)
CATEGORY_B - is a Boolean field (1/0)
CATEGORY_C - is a Boolean field (1/0)
CATEGORY_D - is a Boolean field (1/0)

My output and desired output is:

sql-OUTPUT
sql-OUTPUT.jpg1296×469 91.3 KB

How my query should look like to get the desired output?

2

I think the code below will give you what you need. The key is using "WITH ROLLUP" after the GROUP BY, which generates a grand total with a NULL key value(s).

SELECT
Total_eamil_count, 
TOTALSPENT, TOTALSPENT_BOOKS, TOTALSPENT_SHOES,
CATEGORY_A_count, CATEGORY_B_count, CATEGORY_C_count, CATEGORY_D_count
FROM (
    SELECT dc.email, SUM(1) AS Total_eamil_count, SUM(bft.amount_usd) AS TOTALSPENT,
    SUM(CASE WHEN doct.Product_name = 'Books' THEN bft.amount_usd ELSE 0 END) TOTALSPENT_BOOKS,
    SUM(CASE WHEN doct.Product_name = 'Shoes' THEN bft.amount_usd ELSE 0 END) TOTALSPENT_SHOES,
    COUNT(DISTINCT CASE WHEN doct.CATEGORY_A = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_A_count,
    COUNT(DISTINCT CASE WHEN doct.CATEGORY_B = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_B_count,
    COUNT(DISTINCT CASE WHEN doct.CATEGORY_C = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_C_count,
    COUNT(DISTINCT CASE WHEN doct.CATEGORY_D = 1 THEN doct.order_guid ELSE NULL END) CATEGORY_D_count
    FROM ProductTypeTable doct
        INNER JOIN customerTable dc ON dc.customerID = doct.customerID
        INNER JOIN transactionsTable bft ON bft.transactionID = doct.transactionID
    GROUP BY dc.email WITH ROLLUP
) AS derived
WHERE email IS NULL
3

Thank you for your response.

I have a few questions so I understand this correctly.

  1. In my original question, what exactly "group by dc.email" is doing?
  2. why are we doing "where dc.email IS NULL"? Shouldn't it be NOT NULL?
  3. If I want to keep the complete query as-is (like you gave me) and then supply a list of email addresses to add where clause......where dc.email in (list here or table name), how it will change our new query that you have provided?
4
  1. GROUP BY combines multiple individual rows into a single row. So if a given email was in 3 different rows in the table, in the final result it would only appear in one row.
  2. Right, sorry. I've removed that. The outer query needs to use NULL because adding the WITH ROLLUP clauses causes SQL to generate a grand total row that's a total of all emails, and SQL uses a NULL key value to distinguish the grand total row(s) it generates from normal rows
  3. In the inner query, in a WHERE clause
1 Like
5

Thank you Scott for your help.
Query worked :slight_smile:

6

Hi Scott,

I need some help with the query. Can you please give me another example of how to break this query with the help of temp tables - so grabbing the fields that we need and put it into a temp table and then run the aggregates on top of the temp table?

The query that we worked on is taking a really long time to execute. Almost 1min