I have a problem creating a SQL Server query.
In summary, the query should get columns that are sum and count, grouped by customerID
, and another column that is a case when by a column that is not used as a grouper column.
My problem is to group only part of the syntax, while the case when column does not need to be grouped.
A sample data, Test:
customerID, 1,2,3,4... InvoiceID, 1234551, 1234552... ProductID, A, B, C... Date, Datetime Income, int
customerID | InvoiceID | ProductID | Date | Income |
---|---|---|---|---|
1 | 1234551 | A | 01/01/2015 | 300 |
2 | 1234552 | B | 02/01/2016 | 300 |
I have a solution, but I am sure there is a more simple solution.
SELECT DISTINCT Test.CustomerId, ISNULL(TBL.Income_2015, 0) AS Income_2015, ISNULL(TBL_2.Income_2016, 0) AS Income_2016, CASE WHEN Test.ProductID = 'A' THEN 'TRUE' ELSE 'FALSE' END AS 'purchase_product_A', TBL_3.Invoices FROM Test LEFT OUTER JOIN (SELECT CustomerId, SUM(Income) AS Income_2015 FROM Test WHERE YEAR(Date) = 2015 GROUP BY CustomerId) TBL ON Test.customerID = TBL.customerID LEFT OUTER JOIN (SELECT CustomerId, SUM(Income) AS Income_2016 FROM Test WHERE YEAR(Date) = 2016 GROUP BY CustomerId) TBL_2 ON Test.customerID = TBL_2.customerID LEFT OUTER JOIN (SELECT CustomerId, COUNT(InvoiceID) AS Invoices FROM Test GROUP BY CustomerId) TBL_3 ON Test.customerID = TBL_3.customerID
To produce:
customerID, 1,2,3... Income_2015, int Income_2016, int Invoices, int Purchase_product_A, boolean
customerID | Income_2015 | Income_2016 | Invoices | Purchase_product_A |
---|---|---|---|---|
1 | 300 | 300 | 2 | TRUE |
10 | 0 | 400 | 1 | FALSE |
Thanks! Nir
Advertisement
Answer
You may use conditional aggregation with a single pass query:
SELECT CustomerId, SUM(CASE WHEN YEAR(Date) = 2015 THEN Income ELSE 0 END) AS Income_2015, SUM(CASE WHEN YEAR(Date) = 2016 THEN Income ELSE 0 END) AS Income_2016, COUNT(InvoiceID) AS Invoices, CASE WHEN COUNT(CASE WHEN ProductID = 'A' THEN 1 END) > 0 THEN 'TRUE' ELSE 'FALSE' END AS [Purchase_product_A] FROM Test GROUP BY CustomerId;