Solved

Get the difference of two SUM Queries in T-SQL

Posted on 2009-05-11
155 Views
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%')
``````
0
Question by:megnin
• 11
• 5

LVL 41

Expert Comment

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

LVL 1

Author Comment

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

ralmada earned 500 total points
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
``````
0

LVL 1

Author Comment

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

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

LVL 41

Expert Comment

missed a comma in line 4 above:
x1.tally1 + x2.tally2 AS tally,

0

LVL 1

Author Comment

Sweet!

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

LVL 1

Author Closing Comment

Thanks a lot!!!
0

LVL 1

Author Comment

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

LVL 1

Author Comment

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

Never mind.  I stuck part of the statement in the wrong place.  :-}
0

LVL 1

Author Comment

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

LVL 41

Expert Comment

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

LVL 1

Author Comment

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

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

Thank you.
Here's a good example I found also:
http://www.4guysfromrolla.com/webtech/071906-1.shtml
0

Join & Write a Comment Already a member? Login.

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simpâ€¦
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!