Select Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Select Help

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-26 : 11:48:13

I have this statement that gives me this error. Can someone please help me.
Thanks
Chad

sg 8120, Level 16, State 1, Line 5
Column 'prodcode.product_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.proddesc from coitem
inner join item on coitem.item = item.item

inner join (
select prodcode.description as proddesc,product_code
from prodcode
group by description)
as PC on PC.product_code = item.product_code

where coitem.stat = 'o' and coitem.qty_shipped > 0
group by item.product_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:51:01

select prodcode.description as proddesc,product_code
from prodcode
group by description

in above statement you either need to apply some aggregate function over Product_Code or add it to group by

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-26 : 11:54:59

Sorry for the ignorance, could I just do:
select prodcode.description as proddesc,product_code
from prodcode
group by description,producy_code
?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 11:58:26

quote:
Originally posted by baze7

Sorry for the ignorance, could I just do:
select prodcode.description as proddesc,product_code
from prodcode
group by description,producy_code
?

yup you can similarly in outer select you need to include PC.proddesc also in group by

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 12:05:31

"select prodcode.description as proddesc,product_code
from prodcode
group by description,producy_code"

If you are not using any Aggregating functions you might as well use DISTINCT. Nt sure if one method is better / faster than the other:


select DISTINCT prodcode.description as proddesc,product_code
from prodcode

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-26 : 13:08:29

OK, I am totally confused? Here is my new statement. Same error. Guess I am not understanding something. Thanks for the help.
select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.description from coitem
inner join item on coitem.item = item.item
inner join (select DISTINCT description,product_code from prodcode group by product_code) as PC on pc.product_code = item.product_code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:11:48

quote:
Originally posted by baze7

OK, I am totally confused? Here is my new statement. Same error. Guess I am not understanding something. Thanks for the help.
select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.description from coitem
inner join item on coitem.item = item.item
inner join (select DISTINCT description,product_code from prodcode ~~group by product_code~~) as PC on pc.product_code = item.product_code
item.product_code,PC.description

should be as above

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 13:15:27

You should use EITHER my Distinct code, or Visakh's GROUP BY (including BOTH fields in the Group By).

You've got only one field in the Group By:

select DISTINCT description,product_code from prodcode group by product_code

Then in the Outer Select you have a SUM and the columns item.product_code and PC.description; these two columns need a GROUP BY clause (right at the end) so that the SUM() aggregate function is defined as being "Grouped By" those two fields.

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 13:16:47

You need to add the keyword GROUP BY in front of Visakh's blue code item.product_code,PC.description

baze7
Yak Posting Veteran

58 Posts

Posted - 2010-01-26 : 13:20:09

quote:
I am confused on the last line here:
select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.description from coitem
inner join item on coitem.item = item.item
inner join (select DISTINCT description,product_code from prodcode group by product_code) as PC on pc.product_code = item.product_code
item.product_code,PC.description

I am confused on the last line

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 13:25:12

[code]select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.description
from coitem
inner join item on coitem.item = item.item
inner join (select DISTINCT description,product_code from prodcode group by product_code) as PC on pc.product_code = item.product_code
GROUP BY item.product_code,PC.description
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 13:27:03

quote:
Originally posted by baze7

quote:
I am confused on the last line here:
select sum((coitem.qty_ordered - coitem.qty_shipped) * coitem.cost) as Total,item.product_code,PC.description from coitem
inner join item on coitem.item = item.item
inner join (select DISTINCT description,product_code from prodcode group by product_code) as PC on pc.product_code = item.product_code
item.product_code,PC.description

I am confused on the last line

why so?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources