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

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

Thanks! Nir

CodePudding user response：

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;