计数记录并放入现场 [英] count record and put into the field

问题描述

我有这张桌子

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

*公司A的MP率为2/3 = 66％，FPercent为1/3 = 33％.
* B公司的百分比为0/1 = 0％，F百分比为1/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?

推荐答案

可以做到这一点:

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  

为了回应您的评论，您只需要在select子句中放置一个单独的查询:

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

这篇关于计数记录并放入现场的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！