Combine three queries - 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

General SQL Server Forums

New to SQL Server Programming

Combine three queries

Author

Topic

v2desperado
Starting Member

2 Posts

Posted - 2009-01-23 : 14:34:56

I am trying to combine O/P three same queries. Each one runs fine on its own but I cannot figure out how to combine them so all the values are in the one query.

Query1
select eb.ID,e.Profile, sum(e.Total),sum(e.Price)
from Inf as e, details as eb
where e.Date between eb.Start and eb.stop and e.Peak='Weekends' and e.Profile=eb.profile group by eb.ID,e.Profile
order by eb.D

Query2
select eb.ID,e.Profile, sum(e.Total),sum(e.Price)
from Inf as e, details as eb
where e.Date between eb.Start and eb.stop and e.Peak='Holiday' and e.Profile=eb.profile group by eb.ID,e.Profile
order by eb.D

Query3
select eb.ID,e.Profile, sum(e.Total),sum(e.Price)
from Inf as e, details as eb
where e.Date between eb.Start and eb.stop and e.Peak='ON' and e.Profile=eb.profile group by eb.ID,e.Profile
order by eb.D

output for the individual query is
Query1
ID,Profile,Total(Hour),Total(Price)---which will give me for weekends
----------------------------------
1,1001,10,1000
2,1001,10,1000

Query2
ID,Profile,Total(Hour),Total(Price)---which will give me for Holiday
----------------------------------
1,1001,2,100
2,1001,2,100

Query2
ID,Profile,Total(Hour),Total(Price)---which will give me for ON days
----------------------------------
1,1001,19,3000
2,1001,19,300

But I want some thing like

ID,Profile,Total(Hour),Total(Price),Total(Hour),Total(Price),Total(Hour),Total(Price)
----------------------------------
1,1001,10,1000,2,100,19,3000
2,1001,10,1000,2,100,19,3000

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-23 : 15:23:56

[code]
select eb.ID,e.Profile
,sum(case when e.peak = 'Weekends' then sum(e.Total) else eo end) as WkndTotal
,sum(case when e.peak = 'Weekends' then sum(e.price) else eo end) as WkndPrice
,sum(case when e.peak = 'Holiday' then sum(e.Total) else eo end) as HolidayTotal
,sum(case when e.peak = 'Holiday' then sum(e.price) else eo end) as HolidayPrice
,sum(case when e.peak = 'ON' then sum(e.Total) else eo end) as ONTotal
,sum(case when e.peak = 'ON' then sum(e.price) else eo end) as ONPrice
from Inf as e
inner join
details as eb
on
e.[profile] = [eb.profile]
where e.Date between eb.Start and eb.stop
group by eb.ID,e.[Profile]
order by eb.D

[/code]

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-23 : 15:26:18

oops!

inner join
details as eb
on
~~e.[profile] = [eb.profile]~~
e.[profile] = eb.[profile]

v2desperado
Starting Member

2 Posts

Posted - 2009-01-23 : 16:38:33

Thxs for replying but still have a problem

I am getting an error

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

and also wat is "eo"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-23 : 23:01:19

I think it is not eo, it is zero and the Query was correct but small modification is there

select eb.ID,e.Profile
,sum(case when e.peak = 'Weekends' then (e.Total) else 0 end) as WkndTotal
,sum(case when e.peak = 'Weekends' then (e.price) else 0 end) as WkndPrice
,sum(case when e.peak = 'Holiday' then (e.Total) else 0 end) as HolidayTotal
,sum(case when e.peak = 'Holiday' then (e.price) else 0 end) as HolidayPrice
,sum(case when e.peak = 'ON' then (e.Total) else 0 end) as ONTotal
,sum(case when e.peak = 'ON' then (e.price) else 0 end) as ONPrice
from Inf as e
inner join
details as eb
on
e.[profile] = [eb.profile]
where e.Date between eb.Start and eb.stop
group by eb.ID,e.[Profile]
order by eb.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources