CODEWITHSUNDEEP
mysqlsql
Rtronic
Rtronic

Reputation: 673

sql select and Count from 2 Table

First Table (company)

id    Name        LOGO
1     iphone     ../763.jpg
2     Sony       ../345.jpg
3     Samsung    ../678.jpg

Second Table (operation)

id   company_id   status
1        1         new
2        1         new
3        1         repaired
4        1         Consists
5        2         new
6        2         new
7        3         repaired
8        3         Consists
9        3         repaired

the result shuld be like that

LOGO         Total      new     repaired    Consists    
../763.jpg     4         2          1          1
../345.jpg     2         2          0          0
../678.jpg     3         0          2          1

Upvotes: 0

Views: 31

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5165

You can use below query as well,

select 
c.logo, 
(select count(1) from operation where company_id = o.company_id group by company_id) 
as Total,
(select count(1) from operation where company_id = o.company_id and status = 'new' 
group by status) as new,
(select count(1) from operation where company_id = o.company_id and status = 
'repaired' group by status) as repaired,
(select count(1) from operation where company_id = o.company_id and status = 
'consists' group by status) as consists
from
company c
inner join operation o
on (c.id = o.company_id);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Use a join and conditional aggregation:

select c.logo, count(*) as total,
       sum( status = 'new' ) as new,
       sum( status = 'repaired' ) as repaired,
       sum( status = 'consists' ) as consists,
from company c left join
     operations o
     on c.id = o.company_id
group by c.logo;

Upvotes: 1

Related Questions