• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Get the difference of two SUM Queries in T-SQL

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
megnin
Asked:
megnin
  • 11
  • 5
1 Solution
 
ralmadaCommented:
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
 
megninAuthor Commented:
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
 
ralmadaCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
megninAuthor Commented:
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
 
megninAuthor Commented:
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
 
ralmadaCommented:
missed a comma in line 4 above:
        x1.tally1 + x2.tally2 AS tally,
 
0
 
megninAuthor Commented:
Sweet!

That was it.  Thank you very much!!!
0
 
megninAuthor Commented:
Thanks a lot!!!
0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
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
 
megninAuthor Commented:
Never mind.  I stuck part of the statement in the wrong place.  :-}
0
 
megninAuthor Commented:
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
 
ralmadaCommented:
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
 
megninAuthor Commented:
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
 
ralmadaCommented:
Np. Check this link to know more about common table expressions (CTE):
http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx 
0
 
megninAuthor Commented:
Thank you.
Here's a good example I found also:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now