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

Open in new window

Dan FloodDeveloperAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I restructured the query (significantly) to reduce the workload for SQL and, imho, to make it easier to understand.  Pls test to determine if it returns proper results.

SELECT    cd_curryr.prod_code,
          f06p_ea, a06p_ea, f06p_gal, a06p_gal, flag06,
          f07p_ea, a07p_ea, f07p_gal, a07p_gal, flag07,
          f08p_ea, a08p_ea, f08p_gal, a08p_gal, flag08,
          cust_deta_f01_ea, cust_deta_a01_ea, cust_deta_f01_gal, cust_deta_a01_gal, cust_deta_prod_flag01, 
          cust_deta_f02_ea, cust_deta_a02_ea, cust_deta_f02_gal, cust_deta_a02_gal, cust_deta_prod_flag02, 
          cust_deta_f03_ea, cust_deta_a03_ea, cust_deta_f03_gal, cust_deta_a03_gal, cust_deta_prod_flag03, 
          cust_deta_f04_ea, cust_deta_a04_ea, cust_deta_f04_gal, cust_deta_a04_gal, cust_deta_prod_flag04,
          cust_deta_f05_ea, cust_deta_a05_ea, cust_deta_f05_gal, cust_deta_a05_gal, cust_deta_prod_flag05, 
          cust_deta_f06_ea, cust_deta_a06_ea, cust_deta_f06_gal, cust_deta_a06_gal, cust_deta_prod_flag06, 
          cust_deta_f07_ea, cust_deta_a07_ea, cust_deta_f07_gal, cust_deta_a07_gal, cust_deta_prod_flag07, 
          cust_deta_f08_ea, cust_deta_a08_ea, cust_deta_f08_gal, cust_deta_a08_gal, cust_deta_prod_flag08,
          cust_deta_f09_ea, cust_deta_a09_ea, cust_deta_f09_gal, cust_deta_a09_gal, cust_deta_prod_flag09,
          cust_deta_f10_ea, cust_deta_a10_ea, cust_deta_f10_gal, cust_deta_a10_gal, cust_deta_prod_flag10,
          cust_deta_f11_ea, cust_deta_a11_ea, cust_deta_f11_gal, cust_deta_a11_gal, cust_deta_prod_flag11, 
          cust_deta_f12_ea, cust_deta_a12_ea, cust_deta_f12_gal, cust_deta_a12_gal, cust_deta_prod_flag12, 
          prod_name
FROM (
    SELECT    prod_code,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              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,
              MAX(prod_name) AS prod_name
    FROM        cust_detail
    WHERE     (prod_type_code = '00') AND (fore_year = 2008) AND (cust_code <> '12345')
    GROUP BY  prod_code
) AS cd_curryr
LEFT OUTER JOIN (
    SELECT    prod_code,
              SUM(cust_deta_f06_ea) AS f06p_ea,
              SUM(cust_deta_a06_ea) AS a06p_ea,
              SUM(cust_deta_f06_gal) AS f06p_gal,
              SUM(cust_deta_a06_gal) AS a06p_gal,
              SUM(cust_deta_f07_ea) AS f07p_ea, 
              SUM(cust_deta_a07_ea) AS a07p_ea,
              SUM(cust_deta_f07_gal) AS f07p_gal,
              SUM(cust_deta_a07_gal) AS a07p_gal,
              SUM(cust_deta_f08_ea) AS f08p_ea,
              SUM(cust_deta_a08_ea) AS a08p_ea,
              SUM(cust_deta_f08_gal) AS f08p_gal,
              SUM(cust_deta_a08_gal) AS a08p_gal
    FROM        cust_detail
    WHERE     (fore_year = 2007) AND (cust_code <> '12345')
    GROUP BY  prod_code
) AS cd_prioryr ON cd_prioryr.prod_code = cd_curryr.prod_code
LEFT OUTER JOIN (
    SELECT    prod_code,
              MAX(CASE WHEN fore_year = 2007 THEN prod_flag06 ELSE NULL END) AS flag06,
              MAX(CASE WHEN fore_year = 2007 THEN prod_flag07 ELSE NULL END) AS flag07,
              MAX(CASE WHEN fore_year = 2007 THEN prod_flag08 ELSE NULL END) AS flag08,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag01 ELSE NULL END) AS cust_deta_prod_flag01,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag02 ELSE NULL END) AS cust_deta_prod_flag02,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag03 ELSE NULL END) AS cust_deta_prod_flag03,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag04 ELSE NULL END) AS cust_deta_prod_flag04,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag05 ELSE NULL END) AS cust_deta_prod_flag05,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag06 ELSE NULL END) AS cust_deta_prod_flag06,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag07 ELSE NULL END) AS cust_deta_prod_flag07,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag08 ELSE NULL END) AS cust_deta_prod_flag08,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag09 ELSE NULL END) AS cust_deta_prod_flag09,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag10 ELSE NULL END) AS cust_deta_prod_flag10,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag11 ELSE NULL END) AS cust_deta_prod_flag11,
              MAX(CASE WHEN fore_year = 2008 THEN prod_flag12 ELSE NULL END) AS cust_deta_prod_flag12
    FROM      fore_prod_flag 
    WHERE     (fore_year IN (2007, 2008))
    GROUP BY  prod_code
) AS prodflags ON prodflags.prod_code = cd_curryr.prod_code 
ORDER BY  cd_curryr.prod_code

Open in new window

0
 
brad2575Commented:
if it is complainng about that field update this

GROUP BY prod_code

to this:

GROUP BY prod_code, cd.cust_code
0
 
Dan FloodDeveloperAuthor Commented:
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.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
brad2575Commented:
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
0
 
Dan FloodDeveloperAuthor Commented:
Invalid column name 'Expr1'.  

:-(
0
 
Dan FloodDeveloperAuthor Commented:
Absolutely perfect - thank you!!
0
 
Scott PletcherSenior DBACommented:
Thanks!  That was some serious re-coding there :-) .
0
All Courses

From novice to tech pro — start learning today.