GROUP BY basics - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY basics

Author

Topic

saltcod
Starting Member

13 Posts

Posted - 2010-02-08 : 09:36:54

Hey all,

I have a basic Group By/aggregate function issue that I can't seem to figure out.

I'm trying to show Admissions (ADM) and Discharges (DIS) by hospital and service (services are:surgery, medicine, ICU, mental health, etc).

I've posted the two queries I used. The first one doesn't work, but the second one does. The only difference is that I don't use ADM and DIS as part of the GROUP BY statement.

Is there a rule for what you put in the GROUP BY area?

Thanks a lot for looking!

Terry

SELECT
Hospital,
Service,
Sum(DIS) AS SumOfDIS,
Sum(ADM) AS SumOfADM,
Month,
Year

FROM MasterSheetQuery
GROUP BY Facility,Service,DIS,ADM,Month,Year
ORDER BY Month, Year

----------------------------------------------------------------

SELECT
Hospital,
Service,
Sum(DIS) AS SumOfDIS,
Sum(ADM) AS SumOfADM,
Month,
Year

FROM MasterSheetQuery
GROUP BY Facility,Service,Month,Year
ORDER BY Month, Year

quote:

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 09:42:09

All the columns in the SELECT and ORDER BY that are NOT parameters to aggregating fucntions (like SUM) need to be included in the GROUP BY.

You can include extra fields in the GROUP BY, but usually that doesn't make sense if they are not also in the SELECT as you won't be able to see why there are additional rows being output


SELECT Hospital,
    Service,
    Sum(DIS) AS SumOfDIS,
    Sum(ADM) AS SumOfADM,
    Month,
    Year

FROM MasterSheetQuery
GROUP BY Facility, Hospital, Service,Month,Year
ORDER BY Month, Year

saltcod
Starting Member

13 Posts

Posted - 2010-02-08 : 10:36:24

Great. Thanks very much for lookink - and for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources