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
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%')
ASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
missed a comma in line 4 above:
x1.tally1 + x2.tally2 AS tally,
x1.tally1 + x2.tally2 AS tally,
ASKER
Sweet!
That was it. Thank you very much!!!
That was it. Thank you very much!!!
ASKER
Thanks a lot!!!
ASKER
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..
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 ASKER
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.
ASKER
Never mind. I stuck part of the statement in the wrong place. :-}
ASKER
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
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
ASKER
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! :-)
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! :-)
Np. Check this link to know more about common table expressions (CTE):
http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx
ASKER
Open in new window