Error 8120 when Group By clause is added 2

[traceytr]

In my SP, the following syntax checks as successful. When I add a Group By clause, I get Error 8120 which tells me that several of my fields are "invalid in the select list" because they are "not contained in either an aggregate function or the GROUP BY clause." Same this happens when I run it in Query Analyzer. Can anyone help me understand this?


select distinct
@session,a.product_catalog_id,b.division,b.division_department,b.division_class,b.division_manufacturer,
h.storename,f.ccnname,b.style,a.style_description,c.color_no,e.name,a.uglc,c.ad_sample,d.mu_percent,
MU_PERCENT_pct = (a.owned_retaila.uglc)/a.owned_retail*100,a.owned_retail,0

from
mp..style a, mp..otb_header b, mp..otb_detail c, mp..price_level d, cas..cas_mcolor e, reftables..ccntable f, reftables..sizepack g,
reftables..stores h
WHERE a.product_catalog_id = @product_catalog_id and
a.product_catalog_id = b.product_catalog_id and
a.style = b.style and
a.style = c.style and
b.division = h.store and
b.division = @Division and
f.ccn = substring(@product_catalog_id,1,3)

 

[rajeevnandanmishra]

Hi traceytr,

SQL server has a constraint that if you are using some normal columns and some aggregate function in the select and have not included all of your normal columns in the group by clause, it generates the error.

Please provide the full select statement (with group by clause), so that we can understand the requirement of group by.

 

[traceytr]

I prefer the following SQL to what I have posted above, but the same error is generated.

/*build the style row, line_no = 0 for style level, line_no = 1 for main WHSE, line_no = 2 for secondary WHSE */
select distinct
@session,a.product_catalog_id,b.division,b.division_department,b.division_class,b.division_manufacturer,
h.storename,f.ccnname,b.style,a.style_description,c.color_no,e.name,a.uglc,c.ad_sample,d.mu_percent,
MU_PERCENT_pct = (a.owned_retail - a.uglc)/a.owned_retail*100,a.owned_retail,0

from
(mp..style a
LEFT JOIN otb_header b ON (a.product_catalog_id = b.product_catalog_id) AND
(a.style = b.style))
LEFT JOIN otb_detail c ON (a.product_catalog_id = c.product_catalog_id) AND
(a.style = c.style)
LEFT JOIN price_level d ON (a.ccn = d.ccn)
LEFT JOIN cas..cas_mcolor e ON (c.color_no = e.colorno)
LEFT JOIN reftables..ccntable f ON (a.ccn = f.ccn)
LEFT JOIN reftables..sizepack g ON (c.size_code = g.sizecode)
LEFT JOIN reftables..stores h ON (b.division = h.store)
WHERE a.product_catalog_id = @product_catalog_id and
a.product_catalog_id = b.product_catalog_id and
a.style = b.style and
b.division = h.store and
b.division = @Division and
f.ccn = substring(@product_catalog_id,1,3)
Group By a.product_catalog_id, h.storename, f.ccnname, a.style, a.style_description

Thanks for your help.

Tracey

 

[rajeevnandanmishra]

Hi traceytr,

try to replace the group by clause with following code
-----------------
a.product_catalog_id, h.storename, f.ccnname, a.style, a.style_description,
b.division, b.division_department,b.division_class,b.division_manufacturer,
c.color_no,e.name,a.uglc,c.ad_sample,d.mu_percent,a.owned_retail,a.uglc,
a.owned_retail
-----------------

I hope this will solve your problem.

 

[traceytr]

It seems to cure most of the error. There is one it still doesn't like -- the message says,

"Error 8120: Column 'b.style' is invalid in the select list because it is not contained in either an Aggregate Function or the GROUP BY clause."

Tracey

 

[ColinM]

Add b.style onto your GROUP BY function

 

[traceytr]

That takes care of Error 8120. A different error is generated. It says,

"Error 252: Cannot group by a bit column."

The only bit field I've included is c.ad_sample.

I tried removing it from the group clause and I am back to Error 8120.

 

[ColinM]

You might have to change your database schema from a bit field to a tinyint. Either that or leave it out of the select clause.

 

[traceytr]

I changed the select to this so I could still get the ad_sample information, and then removed c.ad_sample from the Group By clause. This syntax checks successful.


select distinct
@session,a.product_catalog_id,b.division,b.division_department,b.division_class,b.division_manufacturer,
h.storename,f.ccnname,b.style,a.style_description,c.color_no,e.name,a.uglc,
sample = case when Max(convert(int,c.ad_sample)) = 1 then 'Y' else ' ' end,d.mu_percent,
MU_PERCENT_pct = (a.owned_retail - a.uglc)/a.owned_retail*100,a.owned_retail,0


Whew!! I'm new to SP and this is tough to follow. I don't exactly understand why we had to do all of this, but I'm going to look it over some more. Thank you so much for your help.

Tracey