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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.