SQL SELECT SUM UNION or is it sub-query

Alright, this has nothing to do with Excel or VBA, but I thought it was cool, so I wanted to share.

If you have several queries that you need to add together, you may want to try to SUM() them together in a UNION.

WRONG!!!

At first, I thought that if I could use a UNION and put my three results in one column, I could SUM() them together to get an end result. Similar to this:

SELECT SUM(

query A

UNION

query B

UNION

query C)

However, I couldn't find a way for this to work. Luckily, I found a web page that gave me the answer and I was just a little amazed when I found that you can just add complete queries together as sub-queries.

My queries contained several sub-queries which made them quite complex (for me), so being able to just add them all together once I had them finished, surprised me.

Here is an example of one of my queries, we'll call this one A:

SELECT SUM(P.Revenue*A.Commission) AS Performance_Commission

FROM Agent A, Performance P

WHERE A.Agent_Id IN 

          (SELECT P.Person_Id

          FROM Person P

          WHERE P.Last_Name ='SMITH' AND P.First_Name='JOHN')

AND P.Agent_Id IN

          (SELECT P.Person_Id

          FROM Person P

          WHERE P.Last_Name ='SMITH' AND P.First_Name='JOHN')

AND P.Performance_Date BETWEEN '1/1/2004' AND '12/31/2004')

In this example, John Smith is an agent for several bands that have live performances. John gets a 30% commission off of the total revenue of each performance. So, if the total revenue of all of the performances in 2004 from all of the bands that John manages is equal to $100,000, John would get a $30,000 commission. In this instance, this SELECT query would have a result of 30000.

I had three queries of this nature that returned individual values from sub-queries that referenced several different tables. We'll call them A, B, C. And I needed answer X.

X = A + B + C

SOLUTION:

SELECT(

(sub-query A)

+

(sub-query B)

+

(sub-query C))

AS X

Simply amazing.