union trouble

[dbsql]

Hi all, i have following query and trying to do union but get result different

(

SELECT Mrev = '', SUM(testitem.cramount) - SUM(testitem.DebAmount) AS annualtest, DAY(test.date) AS day

FROM test AS test WITH (NOLOCK) INNER JOIN

testItem AS testItem WITH (NOLOCK) ON test.testKey = testItem.testKey INNER JOIN

testaccount AS act WITH (NOLOCK) ON testItem.testcode = act.actCode AND actType = 'S' AND (act.desc LIKE '%Annual%')

WHERE (test.PostDate BETWEEN @firstdayofmonth AND @lastdayofmonth )

GROUP BY DAY(test.date)



)

union

(

SELECT Mrev = '', SUM(testitem.cramount) - SUM(testitem.DebAmount) AS annualtest, DAY(test.date) AS day

FROM test AS test WITH (NOLOCK) INNER JOIN

testItem AS testItem WITH (NOLOCK) ON test.testKey = testItem.testKey INNER JOIN

testaccount AS act WITH (NOLOCK) ON testItem.testcode = act.actCode AND actType = 'S' AND (act.desc LIKE '%monthly%')

WHERE (test.Date BETWEEN @firstdayofmonth AND @lastdayofmonth )

GROUP BY DAY(test.date)

)



but result i get is

monthly annual day

73699.89 0.00 30
122404.09 0.00 13
208177.18 0.00 29
116061.63 0.00 27

0.00 250.11 27

it should be

116061 250.11 27~~



How can i make it happen

 

[AlexCuse]

The results you posted do not even match column names in your query, or column names in your tables. AFAIK, there aren't many mind readers in this forum (why work on computers when you could make $2.99 a minute ;-) ). Please see this FAQ's on effective posting:

http://www.tek-tips.com/faqs.cfm?fid=874

If you can make clear what it is that you are actually after here, that would exponentially increase your chances of getting help.

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[arrowhouse]

Lets see should I be getting that $2.99 a min....

I think what you are saying is that you were expecting one row back from your union for day 27 but you are getting two. But union just joins together 2 result sets (removing results that are duplicated between the 2 result sets unless you specify UNION ALL).

You seem to be expecting an aggregate. To acheive this you would need to add a little more sql:

SELECT SUM(monthly), SUM(annual), day
FROM
(
<your union statements>
) my_data
GROUP BY day

(Its not clear what your columns are called - I've used the ones in your result set)

Looking at your sql though if the only difference in you selects for the unions is the value of act.desc you might want to take a look at the CASE statement & then turn your select into a single select structured something like:

SELECT
 SUM(CASE WHEN act.desc like '%Annual%' then testitem.cramount- testitem.DebAmount else 0 .0 end) as Annual, 
SUM(CASE WHEN act.desc like '%monthly%' then testitem.cramount- testitem.DebAmount else 0 .0 end) as Monthly,
DAY(test.date) AS day
FROM test AS test WITH (NOLOCK) INNER JOIN

testItem AS testItem WITH (NOLOCK) ON test.testKey = testItem.testKey INNER JOIN

testaccount AS act WITH (NOLOCK) ON testItem.testcode = act.actCode AND actType = 'S' 
WHERE (test.Date BETWEEN @firstdayofmonth AND @lastdayofmonth )

GROUP BY DAY(test.date)