SUM of Sums

1. Member
Join Date
Dec 1969
Posts
70

## SUM of Sums

I can&#039;t get the sum of the following sum calculated:<BR><BR>SELECT SUM<BR> (<BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid=8 AND categoryid=5 AND portid=11 AND countryid=71)<BR> -<BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid BETWEEN 1 AND 6 AND categoryid=5 AND portid=11 AND countryid=71)<BR> )<BR>FROM<BR> tblOADArrival<BR><BR>The sun SELECTs work well and the results are 747627 and 612332. This should give a SUM of 747627 - 612332 = 135295.<BR><BR>The error is:<BR><BR>Server: Msg 130, Level 15, State 1, Line 6<BR>Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

2. Senior Member
Join Date
Dec 1969
Posts
96,118

In what possible way are you making any use of the main<BR> SELECT SUM(stuff) FROM tblOADarrival<BR>???<BR><BR>You aren&#039;t *really* selecting ANYTHING AT ALL from that table, are you???<BR><BR>So you *could* simplify that to:<BR><BR>SELECT <BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid=8 AND categoryid=5 AND portid=11 AND countryid=71)<BR> -<BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid BETWEEN 1 AND 6 AND categoryid=5 AND portid=11 AND countryid=71)<BR> AS theDiff<BR>FROM tblOADArrival<BR><BR>And that will work! (Go try it!) But it will give you ONE RECORD in the resulst *PER* record in the table! All the records will be identical, but that&#039;s beside the point.<BR><BR>So you want to find an easy way to limit the result to one record, if you can.<BR><BR>Maybe something as simple as:<BR><BR>SELECT <BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid=8 AND categoryid=5 AND portid=11 AND countryid=71)<BR> -<BR> (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid BETWEEN 1 AND 6 AND categoryid=5 AND portid=11 AND countryid=71)<BR> AS theDiff<BR>FROM tblOADArrival<BR>WHERE id = 1 <BR><BR>assuming you have an ID field on the table and assuming that id=1 exists.<BR><BR>Another way to do this:<BR><BR>SELECT SUM( Pax * <BR> ( CASE reasonid WHEN 8 THEN 1 <BR> WHEN 1 THEN -1 <BR> WHEN 2 THEN -1 <BR> WHEN 3 THEN -1 <BR> WHEN 4 THEN -1 <BR> WHEN 5 THEN -1 <BR> WHEN 6 THEN -1 <BR> END )<BR>FROM tblOADArrival<BR>WHERE categoryid=5 AND portid=11 AND countryid=71<BR><BR>No idea which is more efficient.<BR><BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: SUM of Sums

surely you don&#039;t need the outer SUM()?<BR><BR><BR>DECLARE @sum1 int;<BR>DECLARE @sum2 int;<BR>SET @sum1 = (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid=8 AND categoryid=5 AND portid=11 AND countryid=71)<BR>SET @sum2 = (SELECT SUM (Pax) FROM tblOADArrival WHERE reasonid BETWEEN 1 AND 6 AND categoryid=5 AND portid=11 AND countryid=71)<BR><BR>SELECT (@sum1 - @sum2) AS sumresult<BR><BR><BR>j<BR>http://rtfm.atrax.co.uk/

4. Senior Member
Join Date
Dec 1969
Posts
19,082

interesting. I now find myself wondering what the original intent was exactly.

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Pure guess:

Trying to get a "score" which is the difference between the "good" answers and the "bad" answers.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Access won't allow that

You can&#039;t do a SELECT in Access without basing it on some table.<BR><BR>But sure, in a SQL Server Stored Proc that&#039;s obviously the way to go.<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
19,082

## is this access then?

did I miss it, or have you got a better idea of the context than me?

8. Senior Member
Join Date
Dec 1969
Posts
96,118

## No idea! I was just commenting...

...in case it *is* Access!<BR><BR>I almost always assume that "newbie" questions like this are from Access users. Most people who have graduated to SQL Server tend to have a better grasp of SQL. But certainly not all!<BR><BR>Actually, looking at the error message:<BR> Server: Msg 130, Level 15, State 1, Line 6 <BR> Cannot perform an aggregate function on an expression containing an aggregate or a subquery. <BR>I think it is safe to assume it is not Access. But does that even look like SQL Server message??<BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
19,082

## RE: No idea! I was just commenting...

&#062; But does that even look like SQL Server message??<BR><BR><BR>erm, yeah, I think it does, actually. I&#039;ll check though....

10. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oh, duh...it was the LINE 6

that was throwing me. But of course, this in a StoredProc no doubt! Hence the line number. Hmmm???<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•