union problem

Question

union problem

SQLRep

SSCrazy

Points: 2052
More actions
April 11, 2007 at 7:28 am

#177163

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.PostDate 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You showed us what you are getting and what you want, but you also need to provide us with some sample data as well (preferable the same data used to provide what you are getting and what you want).

Camilo Torres SSCrazy Points: 2034 More actions · Answer 2

You should union the data first the group it. You are grouping first then union it.

SQLRep SSCrazy Points: 2052 More actions · Answer 3

hi if i try doing is gruping at last quiery in union i get

is invalid in the select list because it is not contained in either an aggregate

data for

test

testkey desc

2341 vendor

2548 ABC Cmpany

testitem

testkey testcode date

2341 acct 03/02/2007

2548 servers 03/25/2007

test account

id actcode acttype desc

1 acct s monthly

2 servers s annual

Camilo Torres SSCrazy Points: 2034 More actions · Answer 4

this is a two step process. nothing wrong with that.

1) declare temp_table and insert the union into it

2) select from temp_table, grouping.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

If you can do it that way, then eliminate the temp table and simply batch the request

SELECT SUM()

FROM

(SELECT...

UNION

SELECT...)

GROUP BY WHATEVER

Eliminates the extra overhead of the temp table.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRep SSCrazy Points: 2052 More actions · Answer 6

i still get same result

0.00 1920.96 19

159761.82 0.00 19

136151.20 0.00 23

0.00 2178.57 23

0.00 15.08 27 <--

116061.63 0.00 27

I want is like

159761.82 1920.96 19

136151.20 2178.57 23

116061.63 15.08 27

Catherine Eibner Ten Centuries Points: 1364 More actions · Answer 7

You need to further aggregate this result set. wrap the select statement you have so far in the followng

Select Sum(monthly) as monthly, Sum(Annual) as Annual, Day

From ({your current SQL statement goes here})

Group By Day

Kindest Regards,

Catherine Eibner
cybner.com.au