Join two grouped results - 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 2005 Forums

Transact-SQL (2005)

Join two grouped results

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 04:07:36

I have a sales table and a Appts table
Each with just two columns.
I can group by weekno individually but I would like to group on both at the same time.
So I get three columns
WeekNo, Sales, Appts

You can see the image below of what I have so far.
these are the queries I have used to group each table separately

Select weekNo, Sum(Amount)as Sales
From Sales
Group by WeekNo

Select weekNo, Count(Appt)as Appts
From Appts
Group by WeekNo

Sachin.Nand

2937 Posts

Posted - 2009-10-08 : 05:21:42

You can use a join on weekno for both the tables to get the result.

PBUH

8022421
Starting Member

45 Posts

Posted - 2009-10-08 : 05:52:18

HI
Trying using this logic and let me know.

SELECT S.WEEKNO ,SUM(AMOUNT) ,T.APT
FROM SALES S,
(SELECT WEEKNO,
COUNT(APPT) APPT
FROM APPTS
GROUP BY WEEKNO) T
WHERE S.WEEKNO = T.WEEKNO
GROUP BY S.WEEKNO,T.APPT

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 06:25:39

Thanks a lot 8022421. that's really helpful.
I thought it was something like that, but couldn't get my head around it.

I've changed your query slightly and it produces the same results.
Have I done something wrong by using MAX?
I wanted to remove the grouping by Appts becasue I thinks it's arbitary isn't it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 06:29:40

[code]
select *
from
(
select weekno, sum(amount) as sales
from sales
group by weekno
) s
inner join
(
select weekno, count(appt) as appts
from appts
group by weekno
) a on s.weekno = a.weekno
[/code]

you might need to use LEFT JOIN if you have weekno without sales or appts

KH
[spoiler]Time is always against us[/spoiler]

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 07:12:23

Thats great also thanks a lot khtan
I've just tried it with the left join and that works great if there are sales but no appts.
But what if there are appts with no sales?
What's the best way to join it then?

thanks again

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 07:14:08

I think I got the answer is it FULL JOIN?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 08:23:50

and what if there are no sales and no appts and you still wants to show the row with 0 sales, 0 appts

then you will need a number table or some sort to LEFT JOIN to sales & appts table

KH
[spoiler]Time is always against us[/spoiler]

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 09:15:52

Ah yes a third table with just days of the week would work... I think.

I'll test it and see if I can get the syntax right.
Thanks for the input

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 09:37:27

Can anyone help me I'm getting in a right mess trying to do this now with my third table (Weeknumbers) This table has only one column (WeekNo) and the values are just 1,2,3,4,5,6,7,8,9,10

I just can't seem to get the thing anywhere near right

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 09:54:45

[code]
select w.weekno, isnull(s.sales, 0) as sales, isnull(a.appts, 0) as appts
from
(
select weekno = 1 union all
select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all select 10
) w
left join
(
select weekno, sum(amount) as sales
from sales
group by weekno
) s on w.weekno = s.weekno
left join
(
select weekno, count(appt) as appts
from appts
group by weekno
) a on w.weekno = a.weekno
[/code]

KH
[spoiler]Time is always against us[/spoiler]

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 10:41:59

It looks so easy when you do it!!!

I looked at your query and was confused at first by the selects and Union all's on the Week table, but I've worked out that that's just creating a row for each week (is that right khtan?)
So if I have 52 weeks in my table I'll just repeat that 52 times.

Thanks so much for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 10:57:38

if you have a tally table or number table you can use that.

or you can also make use of master..spt_values or this F_TABLE_NUMBER_RANGE function

KH
[spoiler]Time is always against us[/spoiler]

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2009-10-08 : 11:52:18

F_TABLE_NUMBER_RANGE !!!
Way too intense for my tiny brain!

Thanks for all your help

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-10-08 : 13:31:02

quote:
select w.weekno, isnull(s.sales, 0) as sales, isnull(a.appts, 0) as appts
from
(
select weekno = 1 union all
select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all select 10
) w
left join
(
select weekno, sum(amount) as sales
from sales
group by weekno
) s on w.weekno = s.weekno
left join
(
select weekno, count(appt) as appts
from appts
group by weekno
) a on w.weekno = a.weekno

Hope can help...but advise to wait pros with confirmation...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources