SQL Server : How To Group Only Part Of The Syntax
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
customerID, 1,2,3... Income_2015, int Income_2016, int Invoices, int Purchase_product_A, boolean
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;
- → 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