I am a trying to neatly separate data by year, I have the following query that accomplishes this, however, the date column (YYYYMM) shows every month of every year. So each annual column is zeros for most rows except the ones for its respective year.
SELECT
DATEFROMPARTS(YEAR(l.Date), MONTH(l.Date), 01) AS YYYYMM,
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = '2018' THEN 1 ELSE 0 END) AS 'UT - 2018',
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = '2019' THEN 1 ELSE 0 END) AS 'UT - 2019',
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = '2020' THEN 1 ELSE 0 END) AS 'UT - 2020',
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = '2018' THEN 1 ELSE 0 END) AS 'AZ - 2018',
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = '2019' THEN 1 ELSE 0 END) AS 'AZ - 2019',
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = '2020' THEN 1 ELSE 0 END) AS 'AZ - 2020',
SUM(CASE WHEN l.State ='NV' AND YEAR(l.Date) = '2020' THEN 1 ELSE 0 END) AS 'NV - 2020'
FROM
dbo.list AS l,
dbo.StatusLogs AS sl
WHERE
sl.ListId = l.Id
GROUP BY
DATEFROMPARTS(YEAR(l.Date), MONTH(l.Date), 01)
ORDER BY
DATEFROMPARTS(YEAR(l.Date), MONTH(l.Date), 01)
What I am trying to do is to just have 12 rows (1 row for each month). Like this:
MM UT - 2018 . NV - 2020
Jan 10 5
Feb 5 8
Dec 6 7
Advertisement
Answer
Learn to use proper, explicit, standard, readable JOIN
syntax.
Then, the fix is to just change the GROUP BY
and SELECT
:
SELECT MONTH(l.Date) AS month,
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = 2018 THEN 1 ELSE 0 END) AS [UT - 2018],
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = 2019 THEN 1 ELSE 0 END) AS [UT - 2019],
SUM(CASE WHEN l.State ='UT' AND YEAR(l.Date) = 2020 THEN 1 ELSE 0 END) AS [UT - 2020],
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = 2018 THEN 1 ELSE 0 END) AS [AZ - 2018],
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = 2019 THEN 1 ELSE 0 END) AS [AZ - 2019],
SUM(CASE WHEN l.State ='AZ' AND YEAR(l.Date) = 2020 THEN 1 ELSE 0 END) AS [AZ - 2020],
SUM(CASE WHEN l.State ='NV' AND YEAR(l.Date) = 2020 THEN 1 ELSE 0 END) AS [NV - 2020]
FROM dbo.list l JOIN
dbo.StatusLogs sl
ON sl.ListId = l.Id
GROUP BY MONTH(l.Date)
ORDER BY MONTH(l.Date);
I also removed a bunch of single quotes. Single quotes should be used only for date and string constants. YEAR()
returns a number, so the comparison should be a number. Column aliases should really not need to be escaped, but if you have non-conforming names, then use square braces. Single quotes confuses the column name with a value.