Sql-server – Show Totals Based On Year

sql serversql-server-2008-r2t-sql

I am attempting to show a breakdown of data like this

userid — 2015 TA — 2015 TI — 2016 TA — 2016 TI

b123 — 26.35 — 2 — 36 — 0

b143 — 26.35 — 2 — 36 — 0

b133 — 26.35 — 2 — 36 — 0

I tried this query, but it is not functioning as I thought. I thought the group by would aggregate my data as I need. 

  Select 
  userid
  ,SUM(amt) As TotalAmt
  ,Sum(inflation) As TotalInflation
  FROM @information
  WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
  GROUP BY userid, d1
  ORDER BY userid ASC

How should I write the query to display data like I need? Below is garbage DDL that illustrates my data structure (of course in a real life scenario each amt would be different for each userid):

  Declare @information Table (userID varchar(25), amt float, inflation float, d1 datetime)
  Insert Into @information VALUES 
('b123', 12.23, 1.00, '01/01/2015 11:00'), 
('b123', 14.12, 1.00, '01/08/2015 11:00')
  ,('b123', 14.00, 0.00, '01/22/2016'), 
('b123', 22.00, 0.00, '02/08/2016'),
  ('b143', 12.23, 1.00, '01/01/2015 11:00'), 
('b143', 14.12, 1.00, '01/08/2015 11:00')
  ,('b143', 14.00, 0.00, '01/22/2016'), 
('b143', 22.00, 0.00, '02/08/2016'),
   ('b133', 12.23, 1.00, '01/01/2015 11:00'), 
('b133', 14.12, 1.00, '01/08/2015 11:00')
  ,('b133', 14.00, 0.00, '01/22/2016'), 
('b133', 22.00, 0.00, '02/08/2016')

Best Answer

There are a couple of ways to pivot the data to get the output you want. There's the PIVOT operator, demonstrated here: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

The syntax for PIVOT can be difficult to remember. A simpler method that usually performs just as well is to nest CASE expressions inside SUM:

SELECT userid
, SUM(CASE WHEN YEAR(d1) = 2015 THEN amt ELSE 0 END) AS [2015 TA]
, SUM(CASE WHEN YEAR(d1) = 2015 THEN inflation ELSE 0 END) AS [2015 TI]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN amt ELSE 0 END) AS [2016 TA]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN inflation ELSE 0 END) AS [2016 TI]
FROM @information
WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
GROUP BY userid
ORDER BY userid

It accomplishes the same pivot without the complexity of the PIVOT operator.

Related Question