Dan Flood
asked on
column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
I've tried various things but can't figure out how to make this query work... it complains about column cd.cust_code:
SELECT (SELECT SUM(cust_deta_f06_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f06p_ea,
(SELECT SUM(cust_deta_a06_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a06p_ea,
(SELECT SUM(cust_deta_f06_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f06p_gal,
(SELECT SUM(cust_deta_a06_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a06p_gal,
(SELECT MAX(prod_flag06) AS Expr1
FROM fore_prod_flag AS pf
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS flag06,
(SELECT SUM(cust_deta_f07_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f07p_ea,
(SELECT SUM(cust_deta_a07_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a07p_ea,
(SELECT SUM(cust_deta_f07_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f07p_gal,
(SELECT SUM(cust_deta_a07_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a07p_gal,
(SELECT MAX(prod_flag07) AS Expr1
FROM fore_prod_flag AS pf
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS flag07,
(SELECT SUM(cust_deta_f08_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f08p_ea,
(SELECT SUM(cust_deta_a08_ea) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a08p_ea,
(SELECT SUM(cust_deta_f08_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS f08p_gal,
(SELECT SUM(cust_deta_a08_gal) AS Expr1
FROM cust_detail AS cd2
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS a08p_gal,
(SELECT MAX(prod_flag08) AS Expr1
FROM fore_prod_flag AS pf
WHERE (cd.cust_code <> '12345') AND (prod_code = cd.prod_code) AND (fore_year = 2007)) AS flag08, SUM(cust_deta_f01_ea)
AS cust_deta_f01_ea, SUM(cust_deta_a01_ea) AS cust_deta_a01_ea, SUM(cust_deta_f01_gal) AS cust_deta_f01_gal, SUM(cust_deta_a01_gal)
AS cust_deta_a01_gal,
(SELECT MAX(prod_flag01) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag01, SUM(cust_deta_f02_ea) AS cust_deta_f02_ea,
SUM(cust_deta_a02_ea) AS cust_deta_a02_ea, SUM(cust_deta_f02_gal) AS cust_deta_f02_gal, SUM(cust_deta_a02_gal) AS cust_deta_a02_gal,
(SELECT MAX(prod_flag02) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag02, SUM(cust_deta_f03_ea) AS cust_deta_f03_ea,
SUM(cust_deta_a03_ea) AS cust_deta_a03_ea, SUM(cust_deta_f03_gal) AS cust_deta_f03_gal, SUM(cust_deta_a03_gal) AS cust_deta_a03_gal,
(SELECT MAX(prod_flag03) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag03, SUM(cust_deta_f04_ea) AS cust_deta_f04_ea,
SUM(cust_deta_a04_ea) AS cust_deta_a04_ea, SUM(cust_deta_f04_gal) AS cust_deta_f04_gal, SUM(cust_deta_a04_gal) AS cust_deta_a04_gal,
(SELECT MAX(prod_flag04) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag04, SUM(cust_deta_f05_ea) AS cust_deta_f05_ea,
SUM(cust_deta_a05_ea) AS cust_deta_a05_ea, SUM(cust_deta_f05_gal) AS cust_deta_f05_gal, SUM(cust_deta_a05_gal) AS cust_deta_a05_gal,
(SELECT MAX(prod_flag05) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag05, SUM(cust_deta_f06_ea) AS cust_deta_f06_ea,
SUM(cust_deta_a06_ea) AS cust_deta_a06_ea, SUM(cust_deta_f06_gal) AS cust_deta_f06_gal, SUM(cust_deta_a06_gal) AS cust_deta_a06_gal,
(SELECT MAX(prod_flag06) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag06, SUM(cust_deta_f07_ea) AS cust_deta_f07_ea,
SUM(cust_deta_a07_ea) AS cust_deta_a07_ea, SUM(cust_deta_f07_gal) AS cust_deta_f07_gal, SUM(cust_deta_a07_gal) AS cust_deta_a07_gal,
(SELECT MAX(prod_flag07) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag07, SUM(cust_deta_f08_ea) AS cust_deta_f08_ea,
SUM(cust_deta_a08_ea) AS cust_deta_a08_ea, SUM(cust_deta_f08_gal) AS cust_deta_f08_gal, SUM(cust_deta_a08_gal) AS cust_deta_a08_gal,
(SELECT MAX(prod_flag08) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag08, SUM(cust_deta_f09_ea) AS cust_deta_f09_ea,
SUM(cust_deta_a09_ea) AS cust_deta_a09_ea, SUM(cust_deta_f09_gal) AS cust_deta_f09_gal, SUM(cust_deta_a09_gal) AS cust_deta_a09_gal,
(SELECT MAX(prod_flag09) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag09, SUM(cust_deta_f10_ea) AS cust_deta_f10_ea,
SUM(cust_deta_a10_ea) AS cust_deta_a10_ea, SUM(cust_deta_f10_gal) AS cust_deta_f10_gal, SUM(cust_deta_a10_gal) AS cust_deta_a10_gal,
(SELECT MAX(prod_flag10) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag10, SUM(cust_deta_f11_ea) AS cust_deta_f11_ea,
SUM(cust_deta_a11_ea) AS cust_deta_a11_ea, SUM(cust_deta_f11_gal) AS cust_deta_f11_gal, SUM(cust_deta_a11_gal) AS cust_deta_a11_gal,
(SELECT MAX(prod_flag11) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag11, SUM(cust_deta_f12_ea) AS cust_deta_f12_ea,
SUM(cust_deta_a12_ea) AS cust_deta_a12_ea, SUM(cust_deta_f12_gal) AS cust_deta_f12_gal, SUM(cust_deta_a12_gal) AS cust_deta_a12_gal,
(SELECT MAX(prod_flag12) AS Expr1
FROM fore_prod_flag AS pf
WHERE (prod_code = cd.prod_code) AND (fore_year = 2008)) AS cust_deta_prod_flag12, prod_code, MAX(prod_name) AS prod_name
FROM cust_detail AS cd
WHERE (prod_type_code = '00') AND (fore_year = 2008) AND (cust_code <> '12345')
GROUP BY prod_code
ORDER BY prod_code
ASKER
Hi,
Yes that will return results, however instead of presenting the sum total of each prod_code I then get the sums grouped on individual customers.
Yes that will return results, however instead of presenting the sum total of each prod_code I then get the sums grouped on individual customers.
Keep that and then put this around the entire query. This will give you a sum of all your totals.
select sum(Expr1)
From (
Your query qbove with my group by fix
) as Report
select sum(Expr1)
From (
Your query qbove with my group by fix
) as Report
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Invalid column name 'Expr1'.
:-(
:-(
ASKER
Absolutely perfect - thank you!!
Thanks! That was some serious re-coding there :-) .
GROUP BY prod_code
to this:
GROUP BY prod_code, cd.cust_code