CODEWITHSUNDEEP
sqlsql-servergroup-by
MattR
MattR

Reputation: 5126

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?

Upvotes: 1

Views: 3172

Answers (4)

Munavvar
Munavvar

Reputation: 821

Try with statement and also change your group by condition.

;With CTE AS
(    
    SELECT 
         datepart( year,dbo.PUBACC_HD.grant_date) as Year,
         dbo.PUBACC_HD.radio_service_code as ServiceCode,
         count(dbo.PUBACC_FR.transmitter_make) as NumberofRecords        
    FROM dbo.PUBACC_FR 
    INNER JOIN dbo.PUBACC_HD 
    ON dbo.PUBACC_FR.unique_system_identifier = dbo.PUBACC_HD.unique_system_identifier
)
Select * from cte
GROUP BY  Year, ServiceCode
ORDER BY NumberofRecords desc

As @Lucas Kot-Zaniewski state you are using year in select and group it by date, thats is problem.

Upvotes: 2

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Change Group By To:

Group By datepart( year,dbo.PUBACC_HD.grant_date),dbo.PUBACC_HD.radio_service_code

You have only select year from grant_date, so you have to also write group by accordingly

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

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.

Upvotes: 4

Luke Kot-Zaniewski
Luke Kot-Zaniewski

Reputation: 1161

Because you are grouping by grant_date and not by year

Upvotes: 2

Related Questions