Ad

MS SQL | How To Query A Filtered Column (WHERE) With Non Filtered Data

- 1 answer

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

customerIDInvoiceIDProductIDDateIncome
11234551A01/01/2015300
21234552B02/01/2016300

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

YearMonthcustomersInvoicesProductsTotal_IncomeTotal_Income_A
201513441600600
2015211112000

Thanks! Nir

Ad

Answer

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

Ad
source: stackoverflow.com
Ad