Sub Query - 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

Sub Query

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-08 : 16:06:18

Hi,

I have a requiremnet wherein I need to list all the shop names and the managers which gets the highest no of calls. There can be more than one manager associated with the shop...but we need to find only those who have maximum no of calls.

The query below gives me all the managers and their call with each shop...

select t.shop_name,t.manager_code,t.number_of_total_calls
from
(
select
shop_Name,
manager_code,
sum(Number_of_Total_Calls) as Number_Of_Total_Calls
from
dbo.Facilities
--where
-- Number_of_Total_Calls = (SELECT MAX(Number_of_Total_Calls) FROM dbo.Facilities )
group by
Shop_Name,
Manager_Code
) t
order by Number_Of_Total_Calls desc

The output that we want should be

Shop ManagerCode Total No of Calls
1. 265 100
2. 300 250

Please advice ..

Thank you,

Dp

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-09 : 13:47:46

just a simple correction:


select 
t.shop_name
,t.manager_code
,max(t.number_of_total_calls) as max_nof_calls
from
(
select
shop_Name,
manager_code,
sum(Number_of_Total_Calls) as Number_Of_Total_Calls
from
dbo.Facilities
group by
Shop_Name,
Manager_Code
) t
group by
t.shop_name
,t.manager_code
order by max_nof_calls desc

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-11 : 09:36:09

Hi slimt_slimt,

Thank you for your prompt response. I tried implementing this query

select
t.shop_name
,t.manager_code
,max(t.number_of_total_calls) as max_nof_calls
from
(
select
shop_Name,
manager_code,
sum(Number_of_Total_Calls) as Number_Of_Total_Calls
from
dbo.Facilities
group by
Shop_Name,
Manager_Code
) t
group by
t.shop_name
,t.manager_code
order by max_nof_calls desc

But what I am getting is

Shop Name || ManagerCode || Max No Of Calls

ABC 110 200
BCD 120 400
ABC 100 500
BCD 125 250
GDF 300 210

For the Shop ABC there should be only one Manager displayed in the result set. And that manager should have made the max no of calls for the shop ABC. Here what is happening is we are displaying more than one manager for that shop. So I should be like

Shop Name || ManagerCode || Max No Of Calls

BCD 120 400
ABC 100 500

GDF 300 210

Please advice. Thank You,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-11 : 09:41:23

[code]
select shop_Name,
manager_code,
Number_Of_Total_Calls
from
(
select shop_Name,
manager_code,
Number_Of_Total_Calls ,
row_number() over (partition by shop_Name order by Number_Of_Total_Calls desc) as seq
from
(
select
shop_Name,
manager_code,
sum(Number_of_Total_Calls) as Number_Of_Total_Calls
from
dbo.Facilities
group by
Shop_Name,
Manager_Code
)t
)r
where seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dim
Yak Posting Veteran

57 Posts

Posted - 2010-10-11 : 10:23:51

Thank You visakh16. The code works !

Dp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-11 : 12:05:25

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources