CODEWITHSUNDEEP
mysqlsql
Kalenji
Kalenji

Reputation: 407

MySQL Count and sum based on condition

Is there a way to count SN for each PN and sum it based on a condition (in below case Loc)?

create table table1 (
            code int(10) primary key,
            PN varchar(10) not null,
        SN varchar(10) not null,
                Loc varchar(10));

insert into table1 values (1,'T1','a1','a');
insert into table1 values (2,'T1','a2','a');
insert into table1 values (3,'T1','a3','a');
insert into table1 values (4,'T1','a4','b');
insert into table1 values (5,'T1','a5','b');
insert into table1 values (6,'T1','a6','b');
insert into table1 values (7,'T2','a1','a');
insert into table1 values (8,'T2','a2','a');
insert into table1 values (9,'T2','a3','a');
insert into table1 values (10,'T2','a4','b');
insert into table1 values (11,'T2','a5','b');
insert into table1 values (12,'T2','a6','b');
insert into table1 values (13,'T2','a7','b');

The results I try to achieve is:

PN  a   b
T1  3   3
T2  3   4

Upvotes: 1

Views: 77

Answers (3)

Ahmed Yousif
Ahmed Yousif

Reputation: 2348

You can Try this one 

select PN, count(case when Loc = 'a' then 1 else null end) a,  count(case when Loc = 'b' then 1 else null end) b
from table1
group by PN

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This is just conditional aggregation:

select pn, sum(loc = 'a') as a, sum(loc = 'b') as b
from table1
group by pn;

If you have an unknown list of loc values, then you might need a dynamic query. Google "MySQL dynamic pivot".

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use conditional aggregation :

select PN, sum(case when Loc = 'a' then 1 else 0 end) as a,
           sum(case when Loc = 'b' then 1 else 0 end) as b
from table1 t1
group by PN;

Upvotes: 1

Related Questions