MS SQL | How to query a filtered column (WHERE) with non filtered data-CodePudding

I have a problem solving an MS SQL query. in summary, the query should get the date column as two columns, year and month, the count of other columns, the sum of total of a column, and a filtered sum column. what I struggled with was adding the filtered sum column.

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.

WITH CTE_1 AS
(
SELECT  Date,
        COUNT(DISTINCT Test.customerID) AS customers,
        COUNT(Test.InvoiceID) AS Invoices,
        COUNT(Test.ProductID) AS Products,
        Sum(Income) AS Total_Income,
        ISNULL((SELECT Sum(Income) AS Income_A FROM Test ts WHERE ProductID = 'A' AND ts.Date = Test.Date),0) AS Total_Income_A
FROM Test
GROUP BY Test.Date
)
SELECT YEAR(Date) AS Year,
       MONTH(Date) AS Month,
       Sum(customers) AS customers,
       Sum(Invoices) AS Invoices,
       Sum(Products) AS Products,
       Sum(Total_Income) AS Total_Income,
       Sum(Total_Income_A) AS Total_Income_A
FROM CTE_1
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY YEAR(Date), MONTH(Date)

to produce: Year, 2015, 2016... Month, 1, 2, ... customers, int Invoices, int Products, int Total_Income, int Total_Income_A, int

Year	Month	customers	Invoices	Products	Total_Income	Total_Income_A
2015	1	3	4	4	1600	600
2015	2	1	1	1	1200	0

Thanks! Nir

CodePudding user response：

You can directly apply a Conditional Aggregation such as

SELECT YEAR(Date) AS Year,
       MONTH(Date) AS Month,
       COUNT(DISTINCT customerID) AS customers,
       COUNT(DISTINCT InvoiceID) AS Invoices,
       COUNT(ProductID) AS Products,
       SUM(Income) AS Total_Income,
       ISNULL(SUM(CASE WHEN ProductID = 'A' THEN Income END),0) AS Total_Income_A
  FROM Test
 GROUP BY YEAR(Date), MONTH(Date)
 ORDER BY YEAR(Date), MONTH(Date)

Demo