December 8, 2011 at 1:52 am
Great day guys!
I am using Access and I just want to know
if it is possible to use SUM statement within
another SUM statement in one SQL statement?
The situation is that I have 2 tables:
1. Delivery
2. Pull-out
I need to get the Inventory of each store by getting the sum of Delivery less than by the sum of Pull-out items.
Any help would be greatly appreciated! 🙂
I tried this code but it gives me error, the second line gives the error:
SELECT DR_DCATCD,
SUM(SUM(DR_DQTY)-SUM(POUT_QTY)) AS QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD;
I am waiting for your help guys! Thanks! 🙂
December 8, 2011 at 3:18 am
In SQL Server I get this error
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
So I would guess its not possible in Access either.
December 8, 2011 at 3:27 am
Tom,
Yeah, not possible I guess that's why I get error message.
Anyway Tom, any suggestion in the problem I'm having to get the correct result?
Will wait for your cool ideas.
Maraming salamat! (Filipino way of saying Thank you very much)
🙂
December 8, 2011 at 3:30 am
SELECT DR_DCATCD,
SUM(SUM(DR_DQTY)-SUM(POUT_QTY)) AS QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD;
SELECT DR_DCATCD,
SUM(DR_DQTY)-SUM(POUT_QTY) AS QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD;
Raunak J
December 8, 2011 at 3:42 am
Try this
;WITH IntermediateQRY AS (
SELECT DR_DCATCD,
SUM(DR_DQTY)-SUM(POUT_QTY) AS IntermediateQTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD
)
SELECT SUM(IntermediateQTY) AS QTY,
AMT
FROM IntermediateQRY
GROUP BY AMT;
I'm not sure if it will work with Access though. (This is an SQL Server forum)
December 8, 2011 at 3:56 am
Raunak,
Thanks for the suggestion.
I tried to run your suggestion, no error but the figure is incorrect.
I'm hoping that you could give me other suggestions!
Will wait then.
Salamat! (Filipino way of saying Thanks)
🙂
December 8, 2011 at 4:08 am
I missed out the original grouping. :doze:
;WITH IntermediateQRY AS (
SELECT DR_DCATCD,
SUM(DR_DQTY)-SUM(POUT_QTY) AS IntermediateQTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD
)
SELECT DR_DCATCD,
SUM(IntermediateQTY) AS QTY,
AMT
FROM IntermediateQRY
GROUP BY DR_DCATCD, AMT;
December 9, 2011 at 1:18 am
I guess Access probably does not support CTEs.
You could try taking the CTE out and replacing it with a derived table like this
SELECT DR_DCATCD,
SUM(IntermediateQTY) AS QTY,
AMT
FROM (
SELECT DR_DCATCD,
SUM(DR_DQTY)-SUM(POUT_QTY) AS IntermediateQTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD
) DerivedQRY
GROUP BY DR_DCATCD, AMT;
I don't have Access - so I can't confirm whether it supports derived tables either.
December 9, 2011 at 1:53 am
Tom:
I did try to run it on Access, no error but still the
values is not correct. I don't know what to do.
I know you're a busy man but can you please help me?
Surely your suggestions will get me somewhere. Salamat!
🙂
December 9, 2011 at 2:13 am
Firstly, by how much is the value wrong from what you expect. Could it be due to rounding?
You should then break down the problem into smaller steps to find where the error is.
Create the derived table as a View
CREATE VIEW vDerived AS
SELECT DR_DCATCD,
SUM(DR_DQTY) AS SumDR_DQTY,
SUM(POUT_QTY) AS SumPOUT_QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD
Then examine the values in this view, make sure it is getting the correct values.
Are the values SumDR_DQTY, SumPOUT_QTY what you expect.
Next create a query on this view, trying different ways of getting your QTY
SELECT DR_DCATCD,
SUM(SumDR_DQTY - SumPOUT_QTY) AS QTY1,
SUM(SumDR_DQTY) - SUM(SumPOUT_QTY) AS QTY2,
AMT
FROM vDerived
GROUP BY DR_DCATCD, AMT;
See if QTY1 and QTY2 are different - they should be the same
December 9, 2011 at 4:38 am
Instead of:
SELECT DR_DCATCD,
SUM(SUM(DR_DQTY)-SUM(POUT_QTY)) AS QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD;
Do:
DECLARE @DR_DQTY_VAR INT
DECLARE @POUT_QTY_VAR INT
SELECT SUM(DR_DQTY) INTO @DR_DQTY_VAR,
SUM(POUT_QTY) INTO @POUT_QTY_VAR,
(@DR_DQTY_VAR + @POUT_QTY_VAR) AS QTY,
SUM(DR_DQTY*DR_DUPRICE) AS AMT
FROM DRDTL
INNER JOIN
POUT_DTL
ON DRDTL.DR_DICODE=POUT_DTL.POUT_BC
WHERE DR_DDATE BETWEEN #6/1/2009# AND #6/30/2009#
AND DR_DSTORE IN('342','338','333','302','315','303','312','332','316','325','339','129','217','128')
GROUP BY DR_DCATCD;
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
December 9, 2011 at 4:39 am
Must add I haven't tested it.
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
December 11, 2011 at 8:05 pm
derek.colley,
Probably, your suggestion is not working
in Access, any more suggestion?
Will wait, thanks! 🙂
December 12, 2011 at 2:44 am
Not sure how the SQL will differ for Access to MS SQL Server and unfortunately I don't have time to test it.
Do you see what I'm trying to get at though - avoiding a nested SUM, and instead SUMming the two elements separately?
i.e. in an Excel s/sheet you would have A1:A10 with sample data, B1:B10 with sample data, then A11 = SUM(A1:A10), B11 = SUM(B1:B10) and let's say in C11, 'Grand Total', = SUM(A11+B11). Instead of trying to do C11 = SUM(SUM(A1:A10)+SUM(B1:B10)) - as you've discovered, - nested aggregate functions are disallowed.
Play with the SQL code and see if you can modify it to suit. I suspect my SELECT INTO is letting me down somehow. Or perhaps ask someone a bit more experienced in crafting SQL queries? (My speciality is DBA rather than dev).
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply