CODEWITHSUNDEEP
sqlpostgresql
y ramesh rao
y ramesh rao

Reputation: 3004

SQL Aggregate Calls

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

Upvotes: 0

Views: 48

Answers (3)

Strawberry
Strawberry

Reputation: 33945

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;

Upvotes: 0

user330315
user330315

Reputation: 

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;

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
    c.id , 
    c.name ,
    SUM(IF p.state = 'Shipped',1,0) AS count_shipped,
    SUM(IF p.state = 'Pending',1,0) AS count_pending
FROM Customer AS c
LEFT JOIN Products AS p ON p.customer = c.id
GROUP BY c.id

Upvotes: 0

Related Questions