Question: I have two table like 

1) Customer

```
  id  |       name        
------+-------------------
    1 | xyz1
    2 | xyz2
    3 | xyz3
    4 | xyz4
    5 | xyz5

```

2) Products

```
  id  |       name  | customer | state      
------+-------------+----------+-------
    1 | product 1   |    1     |Shipped    
    2 | product 2   |    1     |Pending 
    3 | product 3   |    1     |Shipped 
    4 | product 4   |    1     |Pending 
    5 | product 5   |    2     |Shipped 

```

I want to have a query Something like :

`SELECT name from customer, SELECT count(*) from products where state='SHIPPED', SELECT count(*) from product where state='PENDING' for all developers` which yields below mentioned result :

```
   name  | count_shipped | count_pending      
  -------+---------------+---------------
    xyz1 | 2             | 2
    xyz2 | 1             | 0
    xyz3 | 0             | 0
    xyz4 | 0             | 0
    xyz5 | 0             | 0

```

Answer: 
```
select c.name,
       sum(case when p.state = 'Pending' then 1 else 0 end) as count_pending,
       sum(case when p.state = 'Shipped' then 1 else 0 end) as count_shipped
from customer c 
   left join products p on p.customer = c.id
group by name;

```
You can also use COUNT...

```
SELECT c.name
     , COUNT(CASE WHEN state = 'shipped' THEN 'foo' END) count_shipped 
     , COUNT(CASE WHEN state = 'pending' THEN 'foo' END) count_pending 
  FROM customer c 
  LEFT
  JOIN products p  
    ON p.customer = c.id 
 GROUP  
    BY c.id;

```