percentage / group by? - 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)

percentage / group by?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-12-11 : 09:13:10

Hi,

I have the following query which I am trying to modify. Basically what I want is an extra column called "percentage". I want this value to represent the percentage of rows brought back with the same CITY name.

I think I am going to need an option that brings back all rows (not just once per city), and another one that only brings back the data once per city.

Any suggestions on the best way to do this? Hopefully my explanation makes sense :) I'm really trying to make this as efficient as possible.

Thanks once again!

mike123

Sample Data

viewId,City, percentage,viewDate

1,Los Angeles, 40%,12/10/2006
2,New York, 20%,12/10/2006
3,Miami , 20%,12/10/2006
4,Chicago, 20%,12/10/2006
5,Los Angeles, 40%,12/11/2006

CREATE PROCEDURE dbo.select_views_Last100
(
@mapID int
)

AS SET NOCOUNT ON

SELECT TOP 100 city, viewDate FROM tblMapViews_Log WHERE mapID = @mapID

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 09:16:20

40% + 20% + 20% + 20% + 40% is 100% ?

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-12-11 : 10:03:47

Hi Peso,

I want to show 40% because the city has 2 views, so yes as confusing as it looks thats correct. The reason I have to bring back each row is that users need to be able to see all the rows individually.

Thanks again

mike123

1,Los Angeles, 70%,12/10/2006
2,New York, 10%,12/10/2006
3,Miami , 10%,12/10/2006
4,Chicago, 10%,12/10/2006
5,Los Angeles, 70%,12/11/2006
6,Los Angeles, 70%,12/12/2006
7,Los Angeles, 70%,12/13/2006
8,Los Angeles, 70%,12/14/2006
9,Los Angeles, 70%,12/15/2006
10,Los Angeles, 70%,12/16/2006

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 10:09:17

select the number of times the city occurs within the top 10, divide the result by then and update the percentage column.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-12-12 : 02:00:15

Hi Peter,

Sorry the part I was having problems with is the syntax, what is the best way to select how many times the city occurs and compute the %?

Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources