[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Subquery/Aggregate Functin help...

Posted on 2006-05-19
12
Medium Priority
?
315 Views
Last Modified: 2008-02-01
i have this data:

Pkey   Div     PO_Num     Category   Voucher_amt      PO_Cost    Variance
123    TOM   9505          Cate001         150.00            100.00       50.00
124    TOM   9505          Cate001           75.00           100.00       -25.00
125    TOM   9505          Cate002          200.00           175.00       25.00
126    TOM   9505          Cate003           30.00            85.00       -55.00
127    TOM   9505          Cate003           40.00            85.00       -45.00
128    TOM   9505          Cate003           35.00            85.00       -50.00

as you can see, same division, same po_num, 3 diff category, variance is voucher_amt - po_cost.

how do i select them where in the resulting cursor/table will have something like this?

Div     PO_Num     Category   Voucher_amt      PO_Cost    Variance
TOM   9505          Cate001         225.00            100.00      125.00
TOM   9505          Cate002         200.00            175.00       25.00
TOM   9505          Cate003         105.00              85.00       30.00

im having this error msg:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT 'SUMMARY' as REPORT_TYPE,
SUM(D.VOUCHER_AMT) AS AP_AMT , ((D.CATEGORY)) AS CATEGORY , ((D.DIVISION)) AS DIVISION
, ((D.PROD_NUM)) AS PROD_NUM,
SUM((CASE WHEN D.PKEY=(SELECT TOP 1 pkey FROM ZZGAPVCD WHERE DIVISION=D.DIVISION AND PROD_NUM=D.PROD_NUM AND CATEGORY=D.CATEGORY)  THEN D.UI_EXT_COST ELSE 0 END)) AS PO_COST,
SUM((D.AP_AMT- (CASE WHEN D.PKEY=(SELECT TOP 1 PKEY FROM ZZGAPVCD WHERE DIVISION=D.DIVISION AND PROD_NUM=D.PROD_NUM AND CATEGORY=D.CATEGORY) THEN D.UI_EXT_COST ELSE 0 END))) AS VARIANCE
FROM ZZGAPVCH H JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY


specifically on these lines:
SUM((CASE WHEN D.PKEY=(SELECT TOP 1 pkey FROM ZZGAPVCD WHERE DIVISION=D.DIVISION AND PROD_NUM=D.PROD_NUM AND CATEGORY=D.CATEGORY)  THEN D.UI_EXT_COST ELSE 0 END)) AS PO_COST,
SUM((D.AP_AMT- (CASE WHEN D.PKEY=(SELECT TOP 1 PKEY FROM ZZGAPVCD WHERE DIVISION=D.DIVISION AND PROD_NUM=D.PROD_NUM AND CATEGORY=D.CATEGORY) THEN D.UI_EXT_COST ELSE 0 END))) AS VARIANCE


thanks in advance!
0
Comment
Question by:fcarandangjr
  • 7
  • 5
12 Comments
 
LVL 14

Expert Comment

by:mherchl
ID: 16718237
try this:

SELECT 'SUMMARY' as REPORT_TYPE, SUM(D.VOUCHER_AMT) AS AP_AMT , ((D.CATEGORY)) AS CATEGORY , ((D.DIVISION)) AS DIVISION
, ((D.PROD_NUM)) AS PROD_NUM,
SUM((CASE WHEN xx.PKEY is not null  THEN D.UI_EXT_COST ELSE 0 END)) AS PO_COST,
SUM((D.AP_AMT- (CASE WHEN xx.PKEY is not null THEN D.UI_EXT_COST ELSE 0 END))) AS VARIANCE

FROM ZZGAPVCH H JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
left join ZZGAPVCD xx on xx.DIVISION=D.DIVISION AND xx.PROD_NUM=D.PROD_NUM AND xx.CATEGORY=D.CATEGORY and xx.PKEY = D.FKEY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY
0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718324
obviously on my sample data that PKEY is not null, no way it can be null...

your code is working fine but not the desired output im looking for.
here is what i got...

Div     PO_Num     Category   Voucher_amt      PO_Cost    Variance
TOM   9505          Cate001         225.00            200.00       25.00
TOM   9505          Cate002         200.00            175.00       25.00
TOM   9505          Cate003         105.00            255.00    -150.00

should be...

Div     PO_Num     Category   Voucher_amt      PO_Cost    Variance
TOM   9505          Cate001         225.00            100.00      125.00
TOM   9505          Cate002         200.00            175.00       25.00
TOM   9505          Cate003         105.00              85.00       30.00


thanks!

0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718353
added 200 more points...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Accepted Solution

by:
mherchl earned 2000 total points
ID: 16718382
SELECT 'SUMMARY' as REPORT_TYPE,
       SUM(D.VOUCHER_AMT) AS AP_AMT,
       (D.CATEGORY) AS CATEGORY,
       (D.DIVISION) AS DIVISION,
       (D.PROD_NUM) AS PROD_NUM,
       SUM(CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS PO_COST,
       SUM(D.VOUCHER_AMT) - (CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS VARIANCE
FROM ZZGAPVCH H
JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
JOIN (select min(PKEY) PKEY, DIVISION , PROD_NUM, CATEGORY FROM ZZGAPVCD GROUP BY DIVISION , PROD_NUM, CATEGORY) a on a.DIVISION = D.DIVISION and a.PROD_NUM = D.PROD_NUM and a.CATEGORY = D.CATEGORY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY
0
 
LVL 14

Expert Comment

by:mherchl
ID: 16718398
is PO_Cost same for each row which have same D.DIVISION , D.PROD_NUM, D.CATEGORY?
0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718513
your right, same PO_COST for each (division+prod_num+category)  combination.

thanks....
0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718570
Error msgs i get....

Column 'ZZGAPVCD.PKEY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'A.pkey' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'ZZGAPVCD.UI_EXT_COST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

thanks...
0
 
LVL 14

Expert Comment

by:mherchl
ID: 16718581
ok, then try this:

SELECT 'SUMMARY' as REPORT_TYPE,
       SUM(D.VOUCHER_AMT) AS AP_AMT,
       (D.CATEGORY) AS CATEGORY,
       (D.DIVISION) AS DIVISION,
       (D.PROD_NUM) AS PROD_NUM,
       (D.UI_EXT_COST) AS PO_COST,
       SUM(D.VOUCHER_AMT) - D.UI_EXT_COST AS VARIANCE
FROM ZZGAPVCH H
JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY, D.UI_EXT_COST
0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718614
I looked carefully at this SQL and there was just one problem,

SELECT 'SUMMARY' as REPORT_TYPE,
       SUM(D.VOUCHER_AMT) AS AP_AMT,
       (D.CATEGORY) AS CATEGORY,
       (D.DIVISION) AS DIVISION,
       (D.PROD_NUM) AS PROD_NUM,
       SUM(CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS PO_COST,
       SUM(D.VOUCHER_AMT) - (CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS VARIANCE
FROM ZZGAPVCH H
JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
JOIN (select min(PKEY) PKEY, DIVISION , PROD_NUM, CATEGORY FROM ZZGAPVCD GROUP BY DIVISION , PROD_NUM, CATEGORY) a on a.DIVISION = D.DIVISION and a.PROD_NUM = D.PROD_NUM and a.CATEGORY = D.CATEGORY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY

which should have this line:
       SUM(D.VOUCHER_AMT - CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS VARIANCE

instead of :
       SUM(D.VOUCHER_AMT) - (CASE WHEN D.PKEY = a.PKEY THEN D.UI_EXT_COST ELSE 0 END) AS VARIANCE

and it worked.....

0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718655
SELECT 'SUMMARY' as REPORT_TYPE,
       SUM(D.VOUCHER_AMT) AS AP_AMT,
       (D.CATEGORY) AS CATEGORY,
       (D.DIVISION) AS DIVISION,
       (D.PROD_NUM) AS PROD_NUM,
       (D.UI_EXT_COST) AS PO_COST,
       SUM(D.VOUCHER_AMT) - D.UI_EXT_COST AS VARIANCE
FROM ZZGAPVCH H
JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY, D.UI_EXT_COST

this one works too, and i used this SQL before, but its not really applicable to my case here... we have this program/class written in Visual Foxpro and i need to create the SQL on the fly, and the solution above just wont work, would take me a day to explain to you why and how.... :)

Anyway, ill try the other solution, and we'll see if that works when i pass it to that $#!@#% program, we call it the "black box". thanks again.
0
 
LVL 14

Expert Comment

by:mherchl
ID: 16718662
yes, but if PO_COST is same then this should work too:

SELECT 'SUMMARY' as REPORT_TYPE,
       SUM(D.VOUCHER_AMT) AS AP_AMT,
       (D.CATEGORY) AS CATEGORY,
       (D.DIVISION) AS DIVISION,
       (D.PROD_NUM) AS PROD_NUM,
       (D.UI_EXT_COST) AS PO_COST,
       SUM(D.VOUCHER_AMT) - D.UI_EXT_COST AS VARIANCE
FROM ZZGAPVCH H
JOIN ZZGAPVCD D ON H.PKEY = D.FKEY
 WHERE ((D.DIVISION = 'TOM'))
   AND ((D.PROD_NUM = 9505))
 GROUP BY D.DIVISION , D.PROD_NUM, D.CATEGORY, D.UI_EXT_COST
0
 
LVL 9

Author Comment

by:fcarandangjr
ID: 16718974
again, we build the report file (FRX) dynamically, its not just a regular Visual FoxPro report form. when i put d.ui_ext_cost in GROUP BY, it doesnt get summed on the Totals Line...

Thanks...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question