MS SQL | How To Query A Filtered Column (WHERE) With Non Filtered Data
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
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
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)
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key