How to get percetage in query - 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 2005 Forums

Transact-SQL (2005)

How to get percetage in query

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-30 : 06:19:49

Hi, i have requiremnt to get names from the table and i want to know the percentage of boys and girls from a table.

My table looks as below


Name         Gender
satish		M
ranjita		F
ALDJIYA		F
Aa		F
satish		M
ranjita		F
ranjita		M

I need output as follows

Name	NameCount MalePct  FemalePct
satish   2	  100	    0
ranjita  3	  66.6	    33.3
ALDJIYA  1        0         100
Aa       1	  0	    100

I am able to get Name and Namecount in results. But i need to get Male percentage and female percentage.

The calculation of percentage for each name is
(Number of males/total)*100;

Can you help me to get query for this?

developer :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-30 : 06:22:02

similar question as this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135120

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

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-30 : 07:02:43

But here my requirement is different. Here two things are there for gender column. Male and female. I need two columns of thier percentages. we need to get male & female count of particular name and then calculate percentage on this.
Can you give query for this one?

quote:
Originally posted by khtan

similar question as this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=135120

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

developer :)

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 07:04:52

I might be wrong but this looks a little more complicated given the % is by the specific name and sex, not just one value divided by the sum total.

How about this

DECLARE @Play TABLE (Name varchar(20),Gender char(1))
INSERT INTO @Play
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ALDJIYA','F' UNION ALL
SELECT 'Aa','F' UNION ALL
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ranjita','M'

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name) * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name) * 100) AS FemalePct
FROM @Play a
GROUP BY Name

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-30 : 07:15:55

I am sorry to mention now. Some of the records have the gender as 'U' i dont want to consider them. I tried your query directly by adding this condition. i am getting divde by zero error. how can i avoid this?
My query is as follows

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M' and b.Gender <> 'U') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F' and b.Gender <> 'U') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS FemalePct
FROM @Play a
GROUP BY Name

quote:
Originally posted by parody

I might be wrong but this looks a little more complicated given the % is by the specific name and sex, not just one value divided by the sum total.

How about this

DECLARE @Play TABLE (Name varchar(20),Gender char(1))
INSERT INTO @Play
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ALDJIYA','F' UNION ALL
SELECT 'Aa','F' UNION ALL
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ranjita','M'

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name) * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F') / (SELECT count(0) FROM @Play c WHERE c.Name = a.Name) * 100) AS FemalePct
FROM @Play a
GROUP BY Name

developer :)

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 07:24:09

What is the intended result for U values? To exclude the name completely or just have it null or 0 in both male and female % columns?

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-30 : 07:34:47

What i am looking for is...take a look at below data in a table


Name         Gender
satish		M
satish		F
satish		F
satish          U
satish          M

i want output as

Name   NameCount   Male    Gender
satish  5          50      50

I dont want to consider 'U'. I want to check the percentages for both 'M' and 'U'. But in name count i have consider '5'

quote:
Originally posted by parody

What is the intended result for U values? To exclude the name completely or just have it null or 0 in both male and female % columns?

developer :)

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 07:52:32

OK the div 0 error is happening them becuause you have some names with only 1 entry which is U, or all entries are U (otherwise it works as you describe).

Just deal with your /0 part - replace it with a 1. So you are forcing 0/1 which = 0

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS FemalePct
FROM @Play a
GROUP BY Name

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-30 : 07:56:15

Here is what i have for you

CREATE TABLE #TEST (Name varchar(100), Gender varchar(2))
insert into #TEST
select 'satish','M' UNION ALL
select 'ranjita','F' UNION ALL
select 'ALDJIYA','F' UNION ALL
select 'Aa','F' UNION ALL
select 'satish','M' UNION ALL
select 'ranjita','F' UNION ALL
select 'ranjita','M'

;With CTE As
(
select distinct name,COUNT(*) over (partition by name) as Cnt,
(select SUM(case when gender='M' then 1 else 0 end) from #TEST B where A.name=b.name) as MaleCnt,
(select SUM(case when gender='F' then 1 else 0 end) from #TEST B where A.name=b.name) As FMlCnt
From #TEST A
)
select Name,Cnt as NameCount
,cast(((cast(MaleCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As MalePer
,Cast(((Cast(FMlCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As FemalePer
from CTE
order by Name desc

-------------------------------------------------

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-30 : 08:06:04

Is There Any Other cases for Gender like More then M,F and U if not then this solution will work for you

Declare @T Table (Name Varchar(100), Gender Varchar(2))
insert into @T
select 'Satish','M' Union All
select 'Satish','M' Union All
select 'Satish','U' Union All
select 'Satish','F' Union All
select 'Satish','F'

;With CTE As
(
select distinct name,COUNT(*) over (partition by name) as Cnt,
(select SUM(case when gender='M' then 1 when Gender='U' then .5 else 0 end) from @T B where A.name=b.name) as MaleCnt,
(select SUM(case when gender='F' then 1 when Gender='U' then .5 else 0 end) from @T B where A.name=b.name) As FMlCnt
From @T A
)
select Name,Cnt as NameCount
,cast(((cast(MaleCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As MalePer
,Cast(((Cast(FMlCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As FemalePer
from CTE
order by Name desc

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-30 : 08:12:28

Here is a little optimized version

Declare @T Table (Name Varchar(100), Gender Varchar(2))
insert into @T
select 'Satish','M' Union All
select 'Satish','M' Union All
select 'Satish','U' Union All
select 'Satish','F' Union All
select 'Satish','F'

;With CTE As
(
select distinct name,cast(COUNT(*) over (partition by name) as Decimal(5,2)) as Cnt,
cast((select SUM(case when gender='M' then 1 when Gender='U' then .5 else 0 end) from @T B where A.name=b.name) as Decimal(5,2)) as MaleCnt,
Cast((select SUM(case when gender='F' then 1 when Gender='U' then .5 else 0 end) from @T B where A.name=b.name) as Decimal(5,2)) As FMlCnt
From @T A
)
select Name,Cnt as NameCount
,(MaleCnt*100)/Cnt As MalePer, (FMlCnt*100)/Cnt As FemalePer
from CTE
order by Name desc

----------------------------------------------------------------

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-10-30 : 08:28:26

Thanks Parody. its works for me.

quote:
Originally posted by parody

OK the div 0 error is happening them becuause you have some names with only 1 entry which is U, or all entries are U (otherwise it works as you describe).

Just deal with your /0 part - replace it with a 1. So you are forcing 0/1 which = 0

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS FemalePct
FROM @Play a
GROUP BY Name

developer :)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-30 : 08:56:31

One More Cool Way to do this work with any Gender You want M,F,U,R,E,D,F,,,,, anything you want takes count only on M & F

DECLARE @Play TABLE (Name varchar(20),Gender char(1))
INSERT INTO @Play
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ALDJIYA','F' UNION ALL
SELECT 'Aa','F' UNION ALL
SELECT 'satish','M' UNION ALL
SELECT 'ranjita','F' UNION ALL
SELECT 'ranjita','M' UNION ALL
SELECT 'ranjita','U' UNION ALL
SELECT 'ranjita','U' UNION ALL
SELECT 'satish','U' UNION ALL
SELECT 'satish','U' UNION ALL
SELECT 'satish','F'

;With CTE As
(
select distinct name,SUM(CASE WHEN Gender='M' Or Gender='F' Then 1 else 0 end) As CNT,--COUNT(*) over (partition by name) as Cnt,
(select SUM(case when gender='M' then 1 else 0 end) from @Play B where A.name=b.name) as MaleCnt,
(select SUM(case when gender='F' then 1 else 0 end) from @Play B where A.name=b.name) As FMlCnt
From @Play A group by name
)
select Name,Cnt as NameCount
,cast(((cast(MaleCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As MalePer
,Cast(((Cast(FMlCnt as Decimal(5,2))*100)/cast(Cnt as Decimal(5,2))) as Decimal(5,2)) As FemalePer
from CTE

-------------------------------------------------------------------------------------

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

parody
Posting Yak Master

111 Posts

Posted - 2009-10-30 : 10:33:27

actually you don't need the first set of <> 'U'. And to encompass ashishashish idea of working with any erroneous gender, change second <> U to IN (M,F)

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name AND c.Gender IN ('M','F')) * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name AND c.Gender IN ('M','F')) * 100) AS FemalePct
FROM @Play a
GROUP BY Name

quote:
Originally posted by satish.gorijala

Thanks Parody. its works for me.

quote:
Originally posted by parody

OK the div 0 error is happening them becuause you have some names with only 1 entry which is U, or all entries are U (otherwise it works as you describe).

Just deal with your /0 part - replace it with a 1. So you are forcing 0/1 which = 0

SELECT
Name
,count(0) AS NameCount
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'M' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS MalePct
,convert(decimal(4,1),(SELECT count(0)*1.0 FROM @Play b WHERE b.Name = a.Name AND b.Gender = 'F' and b.Gender <> 'U') / (SELECT replace(count(0),0,1) FROM @Play c WHERE c.Name = a.Name and c.Gender <> 'U') * 100) AS FemalePct
FROM @Play a
GROUP BY Name

developer :)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-31 : 01:05:25

The basic idea is to account to take only M & F genders in calculation and leave any other gender.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Pradip
Starting Member

32 Posts

Posted - 2009-10-31 : 08:12:29

Hi Satish, according to your formula
The calculation of percentage for each name is
(Number of males/total)*100;

the output should you expect as

Name NameCount MalePct FemalePct

satish 2 100 0
ranjita 3 33.3 66.6
ALDJIYA 1 0 100
Aa 1 0 100

which u mention wrong for ranjita.

The answer will be as below
lets consider tst1 as table
create table tst1
(
Name varchar(50),
Gender char(1)
)

select name,
case Gender when 'M' then count(name) else 0 end Malecnt,
case Gender when 'F' then count(name) else 0 end FeMalecnt
into #t1
from tst1 group by name,gender order by name desc

select name,sum(Malecnt) Malecnt ,sum(FemaleCnt) FeMalecnt
into #t11
from #t1
group by name

select name,count(Gender) as Gender_cnt
into #t3
from tst1 group by name

select t1.name,t2.Gender_cnt,
convert(numeric,t1.Malecnt)
/ convert(numeric,t2.Gender_cnt)*100 as t,
convert(numeric(2),t1.FeMalecnt)/convert(numeric(2),t2.Gender_cnt)*100 as t1
from #t11 t1
JOIN #t3 t2 ON t1.Name=t2.Name
order by t1.name desc

pradipjain

matrixmind
Starting Member

9 Posts

Posted - 2009-11-02 : 03:24:12

quote:
Originally posted by satish.gorijala

Hi, i have requiremnt to get names from the table and i want to know the percentage of boys and girls from a table.

My table looks as below

Name Gender
satish M
ranjita F
ALDJIYA F
Aa F
satish M
ranjita F
ranjita M

I need output as follows

Name NameCount MalePct FemalePct
satish 2 100 0
ranjita 3 66.6 33.3
ALDJIYA 1 0 100
Aa 1 0 100

I am able to get Name and Namecount in results. But i need to get Male percentage and female percentage.

The calculation of percentage for each name is
(Number of males/total)*100;

Can you help me to get query for this?

developer :)

with abc
as
(
select gender,Name,Count(*) as [Cnt],(select Count(*)
From TT T Where T.Name=TT.Name Group by Name) as [Total] From TT Group by name,Gender
)
select name,Total,max(Case when Gender='F' then cnt*100/Total else 0 end )[F],
max(Case when Gender='M' then cnt*100/Total else 0 end) [M] From abc
Group by name,Total order by total

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources