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.
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 ScoreWhen I use the following SQL:SELECT fn_chairs.bl_id,bl.name, SUM (fn_chairs.frs_score) as frs_scoreFROM 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_idWHERE fl.is_active = 1GROUP 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_scoreFROM 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_idWHERE fl.is_active = 1GROUP 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_scoreFROM (SELECT c. bl_id,SUM (fn_chairs.frs_score) as frs_scoreFROM fn_chairs c INNER JOIN fl ON fl.bl_id = c.bl_id AND fl.fl_id = c.fl_idWHERE fl.is_active = 1GROUP BY c.bl_id)chINNER JOIN bl on ch.bl_id = bl.bl_id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|