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
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.