SQL Query Compare value from multiple rows and get count-CodePudding

I have following data in a table. I need the count of Pro, Assist, Spec based on PID and the PID should be consider based on the below condition. PRO > ASSIST > Spec

PID table

PID Table

I need the below result PID Result

PID Result

CodePudding user response：

Try this code:

SELECT  
 Id,
 PID,
 (SELECT COUNT(Ptext) FROM PIDTable WHERE Ptext='PRO' AND PID=P.PID)  AS [Pro Count],
 (SELECT COUNT(Ptext) FROM PIDTable WHERE Ptext='Assist' AND PID=P.PID)  AS [Assist  Count],
 (SELECT COUNT(Ptext) FROM PIDTable WHERE Ptext='Spec' AND PID=P.PID)  AS [Spec  Count] 
FROM PID_Table AS P

CodePudding user response：

Most RDBMS support the case statement which you can use to get the desired result. Here is a code sample:

SELECT Id, PID, SUM(case when Ptext= 'Pro' then 1 else 0 end) as ProCount  from PID
GROUP BY Id, PID