Search code examples
sqlsql-servernorthwind

Divide results in two columns depending on the input values? SQL Server

I am using the Nortwind database with SQL Server 2014, I try to make a query to divide the results of the orders in two different years, The format that I want in my query is

category |anio one | anio two

where the years may vary , What I try so far is

SELECT ca.CategoryName , YEAR(o.OrderDate), SUM(ot.UnitPrice*ot.Quantity) as total
FROM Orders o 
INNER JOIN [Order Details] ot ON O.OrderID = ot.OrderID
INNER JOIN Products pro ON ot.ProductID = pro.ProductID
INNER JOIN Categories ca ON pro.CategoryID = ca.CategoryID
GROUP BY ca.CategoryName,YEAR(o.OrderDate)
ORDER BY ca.CategoryName;

This gives me the totals of each category for a different year, 1996-1997-1998 in column YEAR(o.OrderDate)

I want to get for example

CategoryName | 1996 | 1997

Beverages   |53879,20 | 110424,00
Condiments  |19458,30 | 59679,00
....
Solution

  • Use "conditional aggregates".

    SELECT
      ca.CategoryName
    , SUM(case when year(o.OrderDate) = 1996 then ot.UnitPrice * ot.Quantity end) AS total_1996
    , SUM(case when year(o.OrderDate) = 1997 then ot.UnitPrice * ot.Quantity end) AS total_1997
FROM Orders o
INNER JOIN [Order Details] ot ON o.OrderID = ot.OrderID
INNER JOIN Products pro ON ot.ProductID = pro.ProductID
INNER JOIN Categories ca ON pro.CategoryID = ca.CategoryID
where o.OrderDate >= '19960101' and o.OrderDate < '19980101'
GROUP BY
      ca.CategoryName
ORDER BY
      ca.CategoryName

    Basically that means use a case expression inside the aggregate function.

    I case you are wondering why I have not used "between in the where clause: see Bad habits to kick : mis-handling date / range queries