Is it possible? - 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)

Is it possible?

Author

Topic

Lana
Starting Member

6 Posts

Posted - 2002-07-10 : 13:13:41

Hello, I have a table where data that I am interested in looks like so:
A B C
--------
1 1 0
1 2 0
1 2 0
1 5 1

2 1 1
2 1 0
2 3 1

3 5 0
3 4 0

C is a bit field, and I need to produce a report that will list counts of field B grouped by field A where C=1 ( this is easy) and a ratio of those counts to the count of field B grouped by field A without the criteria

So my final result is:

Value Count Ratio
1 4 1/4 or 25%
2 3 2/3 or 66%
3 2 0/2 or 0%

Is it possible? Thank you .

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-10 : 13:19:36


drop table lana
go
create table lana (a tinyint, b tinyint, c bit)
insert lana
select 1,1,0
union all select 1,2,0
union all select 1,2,0
union all select 1,5,1
union all select 2,1,1
union all select 2,1,0
union all select 2,3,1
union all select 3,5,0
union all select 3,4,0
go

select
    a as value,
    count(*) as [count],
    convert(numeric,(select count(*) from lana where a = l.a and c = 1)) /
        convert(numeric,count(*)) as ratio
from
   lana l
group by
   a
go

<O>

Lana
Starting Member

6 Posts

Posted - 2002-07-10 : 13:40:45

Thank you for the prompt response. But I don't think I can use this approach. My table is a very simple version of what I have. It might have hundreds of different values on column A. I need something like:

select A, COUNT(B) FROM table where C=1 GROUP by A/
cast((select Count(B)FROM table where C=1 GROUP by A)as decimal(10,2))/cast(select Count(B)FROM table GROUP by A) as decimal(10,2)) * 100)

Thank you

Lana
Starting Member

6 Posts

Posted - 2002-07-10 : 13:46:39

sorry, i haven't noticed the sql. I going to try it now. Thank you

Lana
Starting Member

6 Posts

Posted - 2002-07-10 : 13:58:26

Thank you for your help. This is exactly what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources