problem about returing the max value count

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

problem about returing the max value count

Author

Topic

stevenchansql
Starting Member

3 Posts

Posted - 2009-09-14 : 07:44:21

I am new to SQL and have a question regarding the following data,

Table A:
People....Location.....WorkingHours
Sam.......A1...........5
Sam.......A2...........10
Peter.....A1...........5
Peter.....A2...........1
Cris......A3...........20

the result should be

People....Location.....WorkingHours
Sam.......A2...........15
Peter.....A1...........6
Cris......A3...........20

which means summing up the workinghours by people and returning the location where the person has the maximum working hours.

my query
select people, location, sum(workinghours) from tableA
group by people, location

Any idea for me....how can I modify the above code....Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-14 : 07:58:24

[code]
select people, max(location), sum(workinghours)
from tableA
group by people, ~~location~~
[/code]

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

stevenchansql
Starting Member

3 Posts

Posted - 2009-09-14 : 08:09:11

quote:
Originally posted by khtan

select people, max(location), sum(workinghours)
from tableA
group by people, ~~location~~

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

But I wanna return the location with maximum working hours...not the max location...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-14 : 08:13:33

Are you using SQL 2000 or 2005 / 2008 ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-14 : 08:17:48

for SQL 2005 / 2008


declare @tableA table
(
	People		varchar(5),
	Location	char(2),
	WorkingHours	int
)
insert into @tableA
select	'Sam',   'A1',  5 union all
select	'Sam',   'A2', 10 union all
select	'Peter', 'A1',  5 union all
select	'Peter', 'A2',  1 union all
select	'Cris',  'A3', 20

select	People, Location, tot_WorkingHours
from
(
	select	*, 
		row_no = row_number() over (partition by People order by WorkingHours desc),
		tot_WorkingHours = sum(WorkingHours) over (partition by People)
	from	@tableA
) a
where	a.row_no	= 1

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

stevenchansql
Starting Member

3 Posts

Posted - 2009-09-14 : 08:57:36

Thanks for the solution...I'm using Sql visualizer
But I have thousands of data...is there any other way to do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-14 : 09:01:16

you only need the query part in blue and change the table name in bold to your actual table name

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources