sqlsql-servert-sqladventureworks

How to get multiple CASE results to appear on within the same row on SQL Server


I'm looking for some help on an outcome I am trying to achieve from AdventureWorks2012 using SQL Server 2014.

The outcome I am trying to achieve is the total sales per sales person for fiscal quarters.

I want the outcome to be 1 sales person per row which there total sales for Q1, Q2, Q3, Q4 (year July 2012 - June 2013)

SELECT DISTINCT 
    pp.LastName, sp.BusinessEntityID AS SalesPersonID,
    SUM(CASE 
           WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN soh.SubTotal END) AS Q1,
    SUM(CASE
           WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN soh.SubTotal END) AS Q2,
    SUM(CASE
           WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN soh.SubTotal END) AS Q3,
    SUM(CASE
           WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN soh.SubTotal END) AS Q4
FROM 
    Sales.SalesPerson sp
INNER JOIN
    Person.Person pp ON sp.BusinessEntityID = pp.BusinessEntityID
INNER JOIN
    Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
WHERE 
    soh.OrderDate BETWEEN '2012-07-01' AND '2013-06-30'
GROUP BY 
    sp.BusinessEntityID, pp.LastName, soh.OrderDate; 

Current example of result;

LastName     SalesPersonID     Q1     Q2     Q3     Q4
---------------------------------------------------------
Alberts          283        2443.24   NULL  NULL   NULL
Alberts          283          NULL   NULL  NULL   32344.342
Alberts          283          NULL   34234  NULL   NULL

What I expect;

LastName     SalesPersonID     Q1     Q2     Q3     Q4
-----------------------------------------------------------
Alberts          283         2443.24   2324  1142   686858
Jones            287         2443.24   2324  1142   686858
Jimenez          299         2443.24   2324  1142   686858

Solution

  • Fix your GROUP BY:

    GROUP BY sp.BusinessEntityID, pp.LastName; 
    

    Remove soh.OrderDate, because you do not want a separate row for each date.

    Note that SELECT DISTINCT is almost never appropriate with GROUP BY, and certainly not for this query.