Aggregate Query Problem - 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

SQL Server Development (2000)

Aggregate Query Problem

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-13 : 12:01:13

Please help Guys,

I have a table which lists addresses/locations of rented/leased properties, I need a query that looks thru the table and tells me the top 5 states with most leases, expressed as a percentage of the total leasing for all states

[table structure]
col1 statecode
col2 Address
col3 zipcode
etc.

each row in the table lists the address/location of a propety

My Desired Report should be like

Top 5 States
------------
1. CA 25.76%
2. FL 16.09%
3. NY 13.13%
4. IL 9.04%
5. MD 8.06%

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-13 : 13:37:20

[code]

select
top 5
a.statecode
[Percent] =
convert(numeric(19,2),round(
(100.00000*a.state_leases) /
(b.total_leases*1.00000)
,2))
from
(
select
aa.statecode,
[state_leases] = count(*)
from
MyTable aa
group by
aa.statecode
) a
cross join
(
select
[total_leases] = count(*)
from
MyTable bb
) b
order by
2 desc,1

[/code]

CODO ERGO SUM

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-06-13 : 14:48:02

quote:

Originally posted by Michael Valentine Jones



select
	top 5
	a.statecode
	[Percent] =
		convert(numeric(19,2),round(
		(100.00000*a.state_leases) /
		(b.total_leases*1.00000)
		,2))
from
	(
	select
		aa.statecode,
		[state_leases] = count(*)
	from
		MyTable aa
	group by
		aa.statecode
	) a
	cross join
	(
	select
		[total_leases] = count(*)
	from
		MyTable bb
	) b
order by
	2 desc,1

CODO ERGO SUM

Absolute Genius....Thanks a million...God Bless

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources