SUM WITHIN SUM

Question

Post reply

SUM WITHIN SUM

omi_real

SSC-Addicted

Points: 487
More actions
December 8, 2011 at 1:52 am

#251764

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! 🙂

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11475 More actions · Answer 1

Tom257

SSChampion

Points: 11475

December 8, 2011 at 3:18 am

#1418558

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.

omi_real SSC-Addicted Points: 487 More actions · Answer 2

omi_real

SSC-Addicted

Points: 487

December 8, 2011 at 3:27 am

#1418561

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)

🙂

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

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

Tom257 SSChampion Points: 11475 More actions · Answer 4

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)

omi_real SSC-Addicted Points: 487 More actions · Answer 5

omi_real

SSC-Addicted

Points: 487

December 8, 2011 at 3:56 am

#1418570

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)

🙂

Tom257 SSChampion Points: 11475 More actions · Answer 6

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;

omi_real SSC-Addicted Points: 487 More actions · Answer 7

omi_real

SSC-Addicted

Points: 487

December 8, 2011 at 11:13 pm

#1419063

@tom Brown

Thanks for the suggestion.

I tried but it's not working in Access, hope you could give me other

options. Simplified statements will be greatly appreciated.

Will wait for you then. Salamat for being so cool! 🙂

Tom257 SSChampion Points: 11475 More actions · Answer 8

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.

omi_real SSC-Addicted Points: 487 More actions · Answer 9

omi_real

SSC-Addicted

Points: 487

December 9, 2011 at 1:53 am

#1419116

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!

🙂

Tom257 SSChampion Points: 11475 More actions · Answer 10

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

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 11

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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

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.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 12

Must add I haven't tested it.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

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.

omi_real SSC-Addicted Points: 487 More actions · Answer 13

omi_real

SSC-Addicted

Points: 487

December 11, 2011 at 8:05 pm

#1419846

derek.colley,

Probably, your suggestion is not working

in Access, any more suggestion?

Will wait, thanks! 🙂

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 14

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:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

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.