SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group By Syntax Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MattC
Starting Member

USA
9 Posts

Posted - 02/05/2013 :  12:24:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 02/05/2013 :  12:27:25  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  12:32:24  Show Profile  Reply with Quote

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


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

Go to Top of Page

MattC
Starting Member

USA
9 Posts

Posted - 02/05/2013 :  13:58:36  Show Profile  Reply with Quote
That worked great - thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/06/2013 :  00:13:32  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000