CODEWITHSUNDEEP
mysql
notuo
notuo

Reputation: 1161

Count the number od times an ID appear with an specifica value from another column

I need the following if you can help me with the syntax I have a table with 2 fields ID and Priority as follows:

ID | Priority |
---------------
101| 1 |
105| 2 |
200| 3 |
104| 4 |
400| 5 |
400| 1 |
102| 2 |
120| 3 |
150| 4 |
210| 5 |
109| 1 |
104| 2 |
200| 3 |
105| 4 |
250| 5 |

What I need is this result (preferably) 

ID  |TP1|TP2|TP3|TP4|TP5|
-------------------------
101 | 1 |   |   |   |   |
102 |   | 1 |   |   |   |
104 |   | 1 |   | 1 |   |
105 |   | 1 |   | 1 |   |
109 | 1 |   |   |   |   |
120 |   |   | 1 |   |   |
150 |   |   |   | 1 |   |
200 |   |   | 2 |   |   |
250 |   |   |   |   | 1 |
400 | 1 |   |   |   | 1 |

At least this one:

ID | P | T |
------------
101| 1 | 1 |
102| 2 | 1 |
104| 2 | 1 |
104| 4 | 1 |
105| 2 | 1 |
105| 4 | 1 |
109| 1 | 1 |
120| 3 | 1 |
150| 4 | 1 |
200| 3 | 2 |
250| 5 | 1 |
400| 1 | 1 |
400| 5 | 1 |

Sorry. I didn't copied this part.

The table has a bunch of ID and each one has a priority (from 1 to 5). What I need is ti count the number of times any ID has been with Priority 1, Priority 2, etc.

If you look at ID 200 appears 2 times with priority 3 Thanks in advance for any hint on this.

Upvotes: 0

Views: 23

Answers (2)

McNets
McNets

Reputation: 10807

Try this:

select   id,
         count(case when Priority = 1 then 1 else null end) as TP1,       
         count(case when Priority = 2 then 1 else null end) as TP2,       
         count(case when Priority = 3 then 1 else null end) as TP3,       
         count(case when Priority = 4 then 1 else null end) as TP4,       
         count(case when Priority = 5 then 1 else null end) as TP5
from     your_table
group by id

Upvotes: 0

Krzyc
Krzyc

Reputation: 397

You can use something like this:

SELECT ID, SUM(IF(Priority = 1, 1, 0)) TP1, SUM(IF(Priority= 2, 1, 0)) TP2, SUM(IF(Priority = 3, 1, 0)) TP3, SUM(IF(Priority = 4, 1, 0)) TP4, SUM(IF(Priority= 5, 1, 0)) TP5 FROM `tab1` GROUP BY ID

Upvotes: 1

Related Questions