Adding two result sets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding two result sets

Author

Topic

crazyme
Starting Member

11 Posts

Posted - 2006-08-28 : 05:25:25

i have got a requirement of finding the total billable hours for a month, but the month is split into two halfs (first fortnight and second fortnight).

We would be able to find the total billable hours for one fortnight by using this query (for the

select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'firsthalf'

and for the second half the same, but changing the where condition period to second half

select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'secondhalf'

Now both would return hours, i need to sum up both which gives the total hours for whole of the month...Hope i'm clear with my requirement.

Else do let me know.

Knowledge grows when shared...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-28 : 05:29:28

select sum(times) as FullTimes
from (
select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15) as times
from yourtable
where the period = 'firsthalf'
union all
select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15) as times
from yourtable
where the period = 'secondhalf'
) t

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 05:35:52

Will this do?


select	sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)
where	the period IN ( 'firsthalf', 'secondhalf')

Peter Larsson
Helsingborg, Sweden

crazyme
Starting Member

11 Posts

Posted - 2006-08-28 : 06:38:37

Peso...u rock...

Spirit working on your query gives me the following error message...
'timesheethrs1' is not a recognized OPTIMIZER LOCK HINTS option.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'period'

All i did was just substituted my column names in your query....

Knowledge grows when shared...

crazyme
Starting Member

11 Posts

Posted - 2006-08-28 : 07:02:57

Peso...isn't it possible adding two individual result set returned.
(For ex, the hours returned for the first half and the total hours returned for the second half).

I thought using a "+" operator would solve the issue, but didn't...

(select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'firsthalf')
+
select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'secondhalf'

Isn't the logic right...it is going to return numeric values, which needs to be added...any thoughts on how 2 build logic, so that i can tackle it myself. One more thing how would i google for this requirement.

Knowledge grows when shared...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:07:24

Yes it is, but why would you? It is probably slower.

Since most of the work in SQL Server is column based, not row based, use it to your advantage!
Check out the query plans and see what difference they produce.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:12:25

[code]select (select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'firsthalf')
+
(select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'secondhalf')[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-28 : 07:45:34

This is the execution plan for my suggestion

  |--Compute Scalar
       |--Compute Scalar
            |--Stream Aggregate
                 |--Table Scan

And here is the execution plan for the subquery summation

  |--Compute Scalar
       |--Nested Loops(Inner Join)
            |--Nested Loops(Inner Join)
            |    |--Constant Scan
            |    |--Compute Scalar
            |         |--Stream Aggregate
            |              |--Table Scan
            |--Compute Scalar
                 |--Stream Aggregate
                      |--Table Scan

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-28 : 11:24:34

quote:
Originally posted by crazyme

Peso...isn't it possible adding two individual result set returned.
(For ex, the hours returned for the first half and the total hours returned for the second half).

I thought using a "+" operator would solve the issue, but didn't...

(select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'firsthalf')
+
select sum(timesheethrs1) + sum(timesheethrs2) + sum(timesheethrs3)...+ sum(timesheethrs15)where the period = 'secondhalf'

Isn't the logic right...it is going to return numeric values, which needs to be added...any thoughts on how 2 build logic, so that i can tackle it myself. One more thing how would i google for this requirement.

Knowledge grows when shared...

What is wrong with Peso's first method?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources