12,551,121 members (51,454 online)
Rate this:
See more:
I have this table

```ID         gender       companyName
1             M         Company A
2             M         Company A
3             F         Company A
4             F         Company B```

I want this result:

```
CompanyName          M           MPercent    F     FPercent
Company A            2              66       1       33
Company B            0              0        1      100```

*MPercent for Company A is 2/3 = 66%, FPercent is 1/3 = 33%.
*MPercent for Company B is 0/1 = 0%, FPercent is 1/1 = 100%.

Possible to get the result by using mysql query only?
Posted 27-Nov-12 22:03pm
melvintcs1.1K
Updated 27-Nov-12 22:04pm
v2
Krunal R 28-Nov-12 4:05am

What have you tried ??

Rate this:

## Solution 1

This will do it:

```SELECT	[companyName],
SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS M,
(SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END)*100)/COUNT(*) AS MPercent,
SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS F,
(SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END)*100)/COUNT(*) AS MPercent
FROM	[tempdb].[dbo].[companyTable]
GROUP BY companyName  ```

In response to your comment, you simply need to put a seperate query within the select clause:

```SELECT  [companyName],
SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS M,
(SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END)*100)/COUNT(*) AS MPercent,
(SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END)*100)/(SELECT COUNT(*) FROM companyTable) AS MTotalPercent,
SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS F,
(SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END)*100)/COUNT(*) AS FPercent,
(SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END)*100)/(SELECT COUNT(*) FROM companyTable) AS FTotalPercent,
FROM    companyTable
GROUP BY companyName```
v2
melvintcs 28-Nov-12 21:48pm

What should i do if i need to add a new percent, which is record/total record:

1. for company A, 3/4 = 75%
2. for company B, 1/4 = 25%

i tried, but i cant get the '4' since there is a GROUP BY inside the query. COUNT(*) give me result of '3' for company A

Top Experts
Last 24hrsThis month
 Suvendu Shekhar Giri 120 Dave Kreskowiak 50 Harpreet05Kaur 40 Mehdi Gholam 35 ppolymorphe 35
 OriginalGriff 3,911 Suvendu Shekhar Giri 1,838 John Simmons / outlaw programmer 1,687 ppolymorphe 1,581 Karthik Bangalore 1,210