MattR MattR - 2 months ago 6
SQL Question

SQL not grouping properly

I am trying to find the number of records for certain service codes, by year - in my database.

The code:

SELECT datepart( year,dbo.PUBACC_HD.grant_date) as'Year',
dbo.PUBACC_HD.radio_service_code as 'Service Code',
count(dbo.PUBACC_FR.transmitter_make) as 'Number of Records'

FROM dbo.PUBACC_FR
INNER JOIN dbo.PUBACC_HD
ON dbo.PUBACC_FR.unique_system_identifier = dbo.PUBACC_HD.unique_system_identifier
GROUP BY dbo.PUBACC_HD.grant_date, dbo.PUBACC_HD.radio_service_code
ORDER BY [Number of Records] desc


Current Result:

Year Service Code Number of Records
----------- ------------ -----------------
2011 CF 11195 <----
2013 CF 2042
2011 CF 1893 <----
2013 CF 1879
2013 CF 1841
2013 CF 1741
2013 CF 1644
2010 CF 1595
2013 MG 1563
2011 CF 1512 <----
2013 CF 1510
2011 CF 1454
2011 CF 1428
2016 CF 1385
2011 CF 1378
2015 MG 1349


I want all of the fields to be aggregated. Example of none aggregations denoted by arrows.
(2011, CF)
is just one example in the large table of things not aggregating correctly.

Anyone know why this is happening?

Answer

You should use:

GROUP BY datepart( year,dbo.PUBACC_HD.grant_date)

instead of:

GROUP BY  dbo.PUBACC_HD.grant_date

As it is right now, you are grouping by a date value, that may differ among records sharing the same radio_service_code value.