COUNT 多种类型的同一列

2022-01-15 00:00:00 sql select mariadb mysql

在我当前的查询中:

SELECT COUNT(WC.ID) AS "Regions" 
FROM WHOLE_FEATURES_PDB_CHAINS AS WC 
;

我 COUNT(WC.ID) AS "Regions" .但是，我们有多个区域，WC.Type 可以是 1,2,3,4.我需要将每种类型的出现计数到 COUNT(WC.ID) AS "Region_1", COUNT(WC.ID) AS "Region_2" ... 取决于 WC.Type.有没有办法在一个查询中解决这个问题?我正在查看 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;

推荐答案

您可以在聚合函数中使用 case when 语句.

You can use case when statement inside your aggregate function.

试试这个.

count(WC.type = 1 then 1 end) as region_1，类似地重复另一列.

count(case when WC.type = 1 then 1 end) as region_1, similarly repeat for another column.

相关文章