SQL Query help - 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)

SQL Query help

Author

Topic

metalgearal
Starting Member

1 Post

Posted - 2004-11-23 : 05:12:32

I'll start this by showing the table I have and the table I want, as it will help to see it while I explain:

Q | A
-------
1 | 3
1 | 1
1 | 1
1 | 2

I want to turn this into:

Q | A | %
-----------
1 | 3 | 25
1 | 1 | 50
1 | 2 | 25

So a new column is created reporting the percentage of that AID out of all the AIDs given for a particular QID. Note that if there were different QIDs they would each have their own %, hence you could have:

Q | A | %
-----------
1 | 3 | 25
1 | 1 | 50
1 | 2 | 25
2 | 1 | 50
2 | 4 | 50

In the original table that could have been 30 1's and 4's for all we know but that doesn't matter (although at some point I might actually need that information too!)

Anyone know how I can do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-11-26 : 06:29:43

quote:
Note that if there were different QIDs they would each have their own %, hence you could have:

Q | A | %
-----------
1 | 3 | 25
1 | 1 | 50
1 | 2 | 25
2 | 1 | 50
2 | 4 | 50

Can you explain this in detail?

Madhivanan

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-26 : 11:10:24

[code]declare @t table (q int ,a int)
insert @t
select 1 , 3 union all
select 1 , 1 union all
select 1 , 1 union all
select 1 , 2 union all
select 2 , 1 union all
select 2 , 4

select q,
a,
100.0 * count(*)/(select count(*) from @t t1 where t1.q=t.q) [%]
from @t t
group by q,a
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-11-29 : 01:42:39

Thanks VIG, your query gives the required result

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources