Not sure why I am getting a certain error

[bostboy]

My query works fine to get what i want but when I changed it a bit to streamline it, it started giving me an error and I am not quite sure why. The original query that works just fine is:

select g1.priceclassid, sum(p3.baseprice * (1 - (c1.discpct/100)) * p2.itemqty) as price, c1.volmin, c1.discpct, c1.voldiscpct
	from comppriceclass as c1
	inner join pomaster as p1 on p1.companyid = c1.companyid and p1.pricetype = c1.pricetype and pomasterid = 4263
	inner join podetail as p2 on p2.pomasterid = p1.pomasterid
	inner join gridmaster as g1 on g1.gridmasterid = p2.gridmasterid and g1.priceclassid = c1.priceclassid
	inner join pricingmodels as p3 on p3.prodarrkey = p2.prodarrkey and p3.pricingmasterid = p1.histpriceid
	where c1.volmin > 0 and c1.voldiscpct > 0
	group by g1.priceclassid
	having sum(p3.baseprice * (1 - (c1.discpct/100)) * p2.itemqty) > c1.volmin

Then when I run the following it gives me the error “#1054 Unknown column c1.volmin in having clause” where the only change I make is in the select part of the query:

select g1.priceclassid
	from comppriceclass as c1
	inner join pomaster as p1 on p1.companyid = c1.companyid and p1.pricetype = c1.pricetype and pomasterid = 4263
	inner join podetail as p2 on p2.pomasterid = p1.pomasterid
	inner join gridmaster as g1 on g1.gridmasterid = p2.gridmasterid and g1.priceclassid = c1.priceclassid
	inner join pricingmodels as p3 on p3.prodarrkey = p2.prodarrkey and p3.pricingmasterid = p1.histpriceid
	where c1.volmin > 0 and c1.voldiscpct > 0
	group by g1.priceclassid
	having sum(p3.baseprice * (1 - (c1.discpct/100)) * p2.itemqty) > c1.volmin

When I add the c1.volmin back into the select clause it runs fine again as:

select g1.priceclassid, c1.volmin
	from comppriceclass as c1
	inner join pomaster as p1 on p1.companyid = c1.companyid and p1.pricetype = c1.pricetype and pomasterid = 4263
	inner join podetail as p2 on p2.pomasterid = p1.pomasterid
	inner join gridmaster as g1 on g1.gridmasterid = p2.gridmasterid and g1.priceclassid = c1.priceclassid
	inner join pricingmodels as p3 on p3.prodarrkey = p2.prodarrkey and p3.pricingmasterid = p1.histpriceid
	where c1.volmin > 0 and c1.voldiscpct > 0
	group by g1.priceclassid
	having sum(p3.baseprice * (1 - (c1.discpct/100)) * p2.itemqty) > c1.volmin

So why does it need c1.volmin in the select clause but not c1.discpct or p2.itemqty or p3.baseprice, all of which are also used in the having clause?

Thanks for the help

[r937]

if you have c1.volmin in the SELECT clause, you also need it in the GROUP BY clause

[bostboy]

Ok, But the one that is giving me the error does not have c1.volmin in the select clause and the error is for the having clause. So I get the error when c1.volmin is not in the select clause but don’t get it when it is. Either way it is in the having clause and it is not in the group by clause. The only thing I am changing in any of these is the select clause. Both group by and having stay exactly the same.

Does it have anything to do with the fact that c1 is the primary table in the from clause?

In any event it works if I put it in the select clause so it is not a huge problem, more curiousity I suppose.

[r937]

[quote=“bostboy, post:4, topic:221272, full:true”]… more curiousity I suppose.
[/quote]

if you have a HAVING clause, it can have both aggregated and non-aggregated columns –

HAVING SUM(foo) > bar

however, any non-aggregated columns in the HAVING must appear in the GROUP BY clause

if there’s a GROUP BY clause, then any non-aggregated column in the SELECT clause must also be in the GROUP BY

simple, really ;o)

note that you can have a column in the GROUP BY clause but not in the SELECT clause (as a non-aggregated column) but that wouldn’t make much sense

example of this last point –

[code]SELECT country, COUNT(*)
FROM …
GROUP
BY country, city
;

country COUNT(*)
USA 23
USA 93
USA 37
USA 42
USA 11
USA 2
USA 21
USA 28
USA 36
Canada 17
Canada 44
Canada 16
Canada 55
Canada 23
[/code]

can you see why that wouldn’t make much sense? what’s the COUNT(*) for Toronto?