Group By Syntax Problem - 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 Syntax Problem

Author

Topic

MattC
Starting Member

9 Posts

Posted - 2013-02-05 : 12:24:38

Below is a SELECT statement that has a syntax problem that I am hoping someone might be able to help with.

Here, in words, is what I am trying to do:

There are 3 tables - building (bl), floor (fl) and chair (fn_chairs). The chair table has a record for each floor in every building. This floor record contains a chair score (frs_score). I want to sum all the chair scores for for all active floors in each building. The result list should contain 3 fields:

Building ID, Building name, Chair Score

When I use the following SQL:


SELECT fn_chairs.bl_id,bl.name, 
SUM (fn_chairs.frs_score) as frs_score
FROM fn_chairs 
     INNER JOIN fl ON (fl.bl_id = fn_chairs.bl_id AND fl.fl_id = fn_chairs.fl_id)
     INNER JOIN bl on fn_chairs.bl_id = bl.bl_id
WHERE fl.is_active = 1
GROUP BY fn_chairs.bl_id

It generates the error: "Column 'bl.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any suggestions would be appreciated! :)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 12:27:25

For the most part, you have to include in your group by list every column that is in the select list that is outside of an aggregate expression. So,

SELECT fn_chairs.bl_id,
       bl.name,
       SUM(fn_chairs.frs_score) AS frs_score
FROM   fn_chairs
       INNER JOIN fl
            ON  (fl.bl_id = fn_chairs.bl_id AND fl.fl_id = fn_chairs.fl_id)
       INNER JOIN bl
            ON  fn_chairs.bl_id = bl.bl_id
WHERE  fl.is_active = 1
GROUP BY
       fn_chairs.bl_id,
       bl.name

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-05 : 12:32:24

[code]
SELECT bl.bl_id,bl.name, ch.frs_score
FROM (SELECT c. bl_id,
SUM (fn_chairs.frs_score) as frs_score
FROM fn_chairs c
INNER JOIN fl
ON fl.bl_id = c.bl_id
AND fl.fl_id = c.fl_id
WHERE fl.is_active = 1
GROUP BY c.bl_id)ch
INNER JOIN bl
on ch.bl_id = bl.bl_id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MattC
Starting Member

9 Posts

Posted - 2013-02-05 : 13:58:36

That worked great - thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 00:13:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources