COUNT个同一列的多种类型 [英] COUNT multiple types of same column
问题描述
在我当前的查询中:
SELECT COUNT(WC.ID) AS "Regions"
FROM WHOLE_FEATURES_PDB_CHAINS AS WC
;
我COUNT(WC.ID) AS "Regions"
.
但是,我们有多个具有WC.Type的区域,可以为1,2,3,4
.我需要根据WC.Type
将每种类型出现的次数计入COUNT(WC.ID) AS "Region_1"
,COUNT(WC.ID) AS "Region_2"
....
有什么办法可以在一个查询中解决这个问题?我正在查看MySQL IF
,但不知道如何将其集成到count函数中.
I COUNT(WC.ID) AS "Regions"
.
However, we have multiple regions with WC.Type can be 1,2,3,4
. I need to count each type occurrence into COUNT(WC.ID) AS "Region_1"
, COUNT(WC.ID) AS "Region_2"
... depending on WC.Type
.
Is there any way to solve this in one query? I am looking at MySQL IF
, yet do not know how to integrate it into the count function.
我需要将其放在一行中(此处显示的查询已减少,这是一个较大的查询)
I need it to be in one row (the shown query here is reduced, it's a larger query)
SELECT COUNT(WC.ID) AS "Region_1" , COUNT(WC.ID) AS "Region_2" ...
如果有人感兴趣,这里是完整的查询:
Here is the complete query if anyone is interested:
SELECT PCS.PDB_id, PCS.Chain, PPA.ENSEMBL_start, PPA.ENSEMBL_end, PPA.eValue, PIN.TITLE AS "pdbTitle", COUNT(WC.ID) AS "Regions"
FROM PDB_Chains AS PCS
LEFT JOIN WHOLE_FEATURES_PDB_CHAINS AS WC ON WC.PDB_CHAIN_ID = PCS.idPDB_chains, PDB_protein_alignment PPA, PDB_INFOS PIN
WHERE PCS.idPDB_chains = PPA.idPDB_Chains
AND PCS.PDB_id = PIN.PDB_ID
AND PPA.idProteins = (SELECT idProteins from Proteins WHERE ENSEMBL_protein_id = "'+submittedID+'")
GROUP BY PCS.PDB_id, PCS.Chain ORDER BY PCS.PDB_id;
这是根据您的回答得出的有效的解决方案
Here's the working solutin based on your kind answers
SELECT PIN.TITLE AS "pdbTitle", COUNT(CASE WHEN WC.STRUCTURAL_FEATURES_ID = 1 then 1 end) AS "PPInterface" , COUNT(CASE WHEN WC.STRUCTURAL_FEATURES_ID = 4 then 1 end) AS "flexibleRegions"
FROM PDB_Chains AS PCS LEFT JOIN WHOLE_FEATURES_PDB_CHAINS AS WC ON WC.PDB_CHAIN_ID = PCS.idPDB_chains, PDB_protein_alignment PPA, PDB_INFOS PIN
WHERE PCS.idPDB_chains = PPA.idPDB_Chains
AND PCS.PDB_id = PIN.PDB_ID
AND PPA.idProteins = (SELECT idProteins from Proteins WHERE ENSEMBL_protein_id = "ENSP00000256078.4")
GROUP BY PCS.PDB_id, PCS.Chain ORDER BY PCS.PDB_id;
推荐答案
您可以在聚合函数中使用when语句时使用大小写.
You can use case when statement inside your aggregate function.
尝试一下.
count(WC.type = 1然后1结束的情况)作为region_1,类似地对另一列重复.
count(case when WC.type = 1 then 1 end) as region_1, similarly repeat for another column.
这篇关于COUNT个同一列的多种类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!