[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Get the difference of two SUM Queries in T-SQL

Posted on 2009-05-11
16
Medium Priority
?
163 Views
Last Modified: 2012-05-06
I the query below, 14) gives me a total and 15) gives me a total.

I did not write the query.  How can I now show the difference between 14) and 15), say as '16) Positions Filled' ?

Thanks
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '14) Total WIA positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '14) Total WIA positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
 
UNION
 
SELECT '15) WIA Positions remaining to be filled' AS measure,
       Sum(txtworksitejobtitlepositionsnumber)    AS tally
FROM   jobtitles
WHERE  (keyemployerid > 200910201)
       AND employername NOT LIKE '%xxx%'
       AND (employername NOT LIKE '%xx NONE xx%')

Open in new window

0
Comment
Question by:megnin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
16 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24358372
Can you give this a try?
;with grandtotal as (
 
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 
 FROM   (SELECT '14) Total WIA positions created' AS measure,
                Sum(positionsavailable)           AS tally1,
                Sum(positionsavailable)           AS temp_tally
FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '14) Total WIA positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
 
UNION
 
SELECT '15) WIA Positions remaining to be filled' AS measure,
       Sum(txtworksitejobtitlepositionsnumber)    AS tally,
       -Sum(txtworksitejobtitlepositionsnumber)    AS temp_tally
 
FROM   jobtitles
WHERE  (keyemployerid > 200910201)
       AND employername NOT LIKE '%xxx%'
       AND (employername NOT LIKE '%xx NONE xx%')
)
 
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) as tally from grandtotal

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 24358408
Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I'm not sure where to fix this...
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 24358433
Sorry, try this:
;with grandtotal as (
 
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
        x1.tally1 + x2.tally2 AS temp_tally
 
 
 FROM   (SELECT '14) Total WIA positions created' AS measure,
                Sum(positionsavailable)           AS tally1
FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '14) Total WIA positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
 
UNION
 
SELECT '15) WIA Positions remaining to be filled' AS measure,
       Sum(txtworksitejobtitlepositionsnumber)    AS tally,
       -Sum(txtworksitejobtitlepositionsnumber)    AS temp_tally
 
FROM   jobtitles
WHERE  (keyemployerid > 200910201)
       AND employername NOT LIKE '%xxx%'
       AND (employername NOT LIKE '%xx NONE xx%')
)
 
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal

Open in new window

0
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.

 
LVL 1

Author Comment

by:megnin
ID: 24358477
It's probably just a character somewhere.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'x1'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'x1'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near 'x2'.
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.

Also, I apologize, I should have remove all the garbage, "AND NOT LIKE '%xxx%'" crap that's in there just to filter out my test records.
0
 
LVL 1

Author Comment

by:megnin
ID: 24358497
This is a little easier to read...
;with grandtotal as (
 
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
        x1.tally1 + x2.tally2 AS temp_tally
 
 
 FROM   (SELECT '14) Total WIA positions created' AS measure,
                Sum(positionsavailable)           AS tally1
FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '14) Total WIA positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
 
UNION
 
SELECT '15) WIA Positions remaining to be filled' AS measure,
       Sum(txtworksitejobtitlepositionsnumber)    AS tally,
       -Sum(txtworksitejobtitlepositionsnumber)    AS temp_tally
 
FROM   jobtitles
WHERE  (keyemployerid > 200910201)
)
 
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24358502
missed a comma in line 4 above:
        x1.tally1 + x2.tally2 AS tally,
 
0
 
LVL 1

Author Comment

by:megnin
ID: 24358512
Sweet!

That was it.  Thank you very much!!!
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 31580292
Thanks a lot!!!
0
 
LVL 1

Author Comment

by:megnin
ID: 24358635
That worked perfectly, but when I tacked it onto the bottom of the rest of the query it didn't like the " ; " after that UNION

I haven't tried just removing it yet..
...the first set of queries...
UNION
...more queries...
UNION
;with grandtotal as ( 
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '05) Total CSC positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '05) Total CSC positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
UNION 
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '14) Total WIA positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '14) Total WIA positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
UNION
SELECT '15) WIA Positions remaining to be filled' AS measure,
       Sum(txtworksitejobtitlepositionsnumber)    AS tally,
       -Sum(txtworksitejobtitlepositionsnumber)    AS temp_tally
 
FROM   jobtitles
WHERE  (keyemployerid > 200910201)
       AND employername NOT LIKE '%xxx%'
       AND (employername NOT LIKE '%xx NONE xx%')
)
 
select measure, tally from grandtotal
union
select '16) Positions filled' as measure, sum(temp_tally) from grandtotal       

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 24358705
I just removed the " ; " and it didn't like that either.  I got "Incorrect syntax near ; or WITH depending on whether the ; was there or not.
0
 
LVL 1

Author Comment

by:megnin
ID: 24358826
Never mind.  I stuck part of the statement in the wrong place.  :-}
0
 
LVL 1

Author Comment

by:megnin
ID: 24358950
Nope, I put everything where it should be.  '05)' goes fine by itself and everything from ;WITH grandtotal  goes fine by itself but I get "Incorrect syntax near ';' - when I put the two together like below:
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '05) Total CSC positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '05) Total CSC positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
UNION 
-- From ralmada at Experts-Exchange
;WITH grandtotal
     AS ((SELECT x1.measure,
                 x1.tally1 + x2.tally2 AS tally,
                 x1.tally1 + x2.tally2 AS temp_tally
          FROM   (SELECT '14) Total WIA positions created' AS measure,
                         Sum(positionsavailable)           AS tally1
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
						 AND employername NOT LIKE '%xxx%'
				         AND (employername NOT LIKE '%xx NONE xx%')
						 AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NOT NULL) x1,
                 (SELECT '14) Total WIA positions created'       AS measure,
                         Sum(txtworksitejobtitlepositionsnumber) AS tally2
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
		                 AND employername NOT LIKE '%xxx%'
				         AND (employername NOT LIKE '%xx NONE xx%')
						 AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NULL) x2
          WHERE  x1.measure = x2.measure)
         UNION 
         SELECT '15) WIA Positions remaining to be filled' AS measure,
                Sum(txtworksitejobtitlepositionsnumber)    AS tally,
                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
				        AND employername NOT LIKE '%xxx%'
						AND (employername NOT LIKE '%xx NONE xx%')
         )
SELECT measure,
       tally
FROM   grandtotal
UNION 
SELECT '16) Positions filled' AS measure,
       Sum(temp_tally)
FROM   grandtotal

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24359419
because you cannot "UNION" a CTE query (the one that starts with "WITH") like that. You can try something like this though.
 

;WITH grandtotal
     AS ((SELECT x1.measure,
                 x1.tally1 + x2.tally2 AS tally,
                 x1.tally1 + x2.tally2 AS temp_tally
          FROM   (SELECT '14) Total WIA positions created' AS measure,
                         Sum(positionsavailable)           AS tally1
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
						 AND employername NOT LIKE '%xxx%'
				         AND (employername NOT LIKE '%xx NONE xx%')
						 AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NOT NULL) x1,
                 (SELECT '14) Total WIA positions created'       AS measure,
                         Sum(txtworksitejobtitlepositionsnumber) AS tally2
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
		                 AND employername NOT LIKE '%xxx%'
				         AND (employername NOT LIKE '%xx NONE xx%')
						 AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NULL) x2
          WHERE  x1.measure = x2.measure)
         UNION 
         SELECT '15) WIA Positions remaining to be filled' AS measure,
                Sum(txtworksitejobtitlepositionsnumber)    AS tally,
                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
				        AND employername NOT LIKE '%xxx%'
						AND (employername NOT LIKE '%xx NONE xx%')
         )
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '05) Total CSC positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '05) Total CSC positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
union
SELECT measure,
       tally
FROM   grandtotal
UNION 
SELECT '16) Positions filled' AS measure,
       Sum(temp_tally)
FROM   grandtotal

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 24359458
I didn't realize you could not "UNION" a CTE query (I just learned that term).

I moved that whole section to the top so it started with the CTE query (I'll still have to read more about that) and it worked fine.  Thank you again for your help!  :-)
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24359573
Np. Check this link to know more about common table expressions (CTE):
http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx 
0
 
LVL 1

Author Comment

by:megnin
ID: 24359581
Thank you.
Here's a good example I found also:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 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