SQL Query UNION question
-
I need to combine two result sets, putting two separate values together. Here is what I have so far:
select state,count(*),0 as total from tAccount
AND datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state
UNION
SELECT state,0,sum(charges) as total from tBilling
AND datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state
The result set is coming back like this:
AL099.65
AL270.00
AR0280.00
AR430.00
I need it to combine the state responses together, to look like this:
AL2799.65
AR43280.00
-
This should work
(note: completly untested)
SELECT state, SUM(total) AS Total FROM (
select state,count(*),0 as total from tAccount
Where datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state
UNION
SELECT state,0,sum(charges) as total from tBilling
Where datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state) a
GROUP BY State
-
It looks like you want to sum the results of your unioned query try something like this:
SELECT [State], SUM(Field1) [Field1], SUM(Total) [Total]
FROM
(select
state,
count(*) [Field1],
0 as total
from tAccount
AND datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state
UNION
SELECT
state,
0,
sum(charges) as total
from tBilling
AND datestamp >= '9/1/2009' AND datestamp <= '9/30/2009'
group by state)) Derived
GROUP BY [State]
I'm not sure what you wanted to call the attribute after state, so I called it Field1.
Hope this helps,
Rich[p]
[/p]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply