need the record count - 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

need the record count

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2011-01-06 : 23:36:01

Hi,

this is the my result table

GID ID Cap
1 001 Y
1 002 N
1 003 Y
2 010 Y
2 011 N
3 012 Y
3 013 N
3 014 Y
4 101 Y
4 102 N

and in the final result set i need the output like this

GID ID Count percentage Cap
1 001 3 100*1/3 Y
1 002 3 100*1/3 N
1 003 3 100*1/3 Y
2 010 2 100*3/2 Y
2 011 2 100*3/2 N
3 012 3 100*6/3 Y
3 013 3 100*6/3 N
3 014 3 100*6/3 Y
4 101 2 100*2/2 Y
4 102 2 100*2/2 N

Here in the count column it should depend on how many IDs are present for each GID and the in percentage that 100*(this number i am getting from other field, that i will take care)/(count of IDs for each GID)...
Example: For one GID if contains 5 IDs then count should be 5..this is the logic i need how to get this ..i am new to this SQL can any one help me out to get the query.

dev

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-06 : 23:46:45

[SNIP]
@Here in the count column it should depend on how many IDs are present for each GID
[SNIP]

for this you an use the following query

Select GID,count(ID) From TableName Group by GID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-06 : 23:54:41

if you are using SQL 2005/2008

the_Count = count(*) over (partition by GID)

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

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-07 : 00:05:04

check this one...
SELECT GID,
ID,
COUNT(GID)AS COUNT,
'100*1/3' AS PERCENTAGE,
CAP FROM TEST11
GROUP BY GID,ID,CAP

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-07 : 00:53:56

PLEASE REPLACE THE CODE WITH THIS CODE.
SELECT GID,
ID,
count(*) over (partition by GID)as COUNT
,
'100*1/3' AS PERCENTAGE,
CAP FROM TEST11
GROUP BY GID,ID,CAP

quote:
Originally posted by ahmeds08

check this one...
SELECT GID,
ID,
COUNT(GID)AS COUNT,
'100*1/3' AS PERCENTAGE,
CAP FROM TEST11
GROUP BY GID,ID,CAP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources