CODEWITHSUNDEEP
sqlpostgresqljoin
Vimal Sangar
Vimal Sangar

Reputation: 31

Postgres - Query to select fields from multiple tables as columns

I have the following tables

Table 1 : Product 

id  name  
1   Bread  
2   Bun  
3   Cake

Table 2: Expense Items 

product| quantity  
1 |      100  
2 |      150  
3 |      180  
1 |      25  
2 |      30

Table 3: Income Items 

product| quantity  
1 |      100  
2 |      150  
3 |      180  
1 |      25  
2 |      30

Now I want the results like this 

product   |  sum of quantity of expenseitem | sum of quantity of income item  
1         |  125 |  125  
2         |  180 |  180  
3         |  180 |  180

What is the query to get this result ?
Thanks

Upvotes: 1

Views: 5140

Answers (2)

Parfait
Parfait

Reputation: 107567

Similar to @D-Shih's answer, PostgreSQL 9.4+ supports the FILTER() clause for conditional aggregation in place of CASE statements:

SELECT p.id,
       SUM(quantity) FILTER (WHERE grp = 1) SUMExpenseItems,
       SUM(quantity) FILTER (WHERE grp = 2) SUMIncomeItems
FROM 
     -- ...same union all query...
GROUP BY p.id

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use UNION ALL in a subquery with the condition in the aggregate function

Schema (PostgreSQL v9.6)

CREATE TABLE Product(
    id int,
    name varchar(50)
);


INSERT INTO Product VALUES (1,'Bread');  
INSERT INTO Product VALUES (2,'Bun');  
INSERT INTO Product VALUES (3,'Cake');  

CREATE TABLE ExpenseItems(
    product int,
    quantity int
);


INSERT INTO ExpenseItems VALUES (1,100);  
INSERT INTO ExpenseItems VALUES (2,150);  
INSERT INTO ExpenseItems VALUES (3,180);  
INSERT INTO ExpenseItems VALUES (1,25);  
INSERT INTO ExpenseItems VALUES (2,30);  


CREATE TABLE IncomeItems(
    product int,
    quantity int
);


INSERT INTO IncomeItems VALUES (1,100);  
INSERT INTO IncomeItems VALUES (2,150);  
INSERT INTO IncomeItems VALUES (3,180);  
INSERT INTO IncomeItems VALUES (1,25);  
INSERT INTO IncomeItems VALUES (2,30);

Query #1

SELECT p.id,
      SUM(CASE WHEN grp = 1 THEN quantity END) SUMExpenseItems,
      SUM(CASE WHEN grp = 2 THEN quantity END) SUMIncomeItems
FROM (
    SELECT product, quantity,1 grp 
    FROM ExpenseItems
    UNION ALL
    SELECT product, quantity,2 
    FROM IncomeItems
) t1 JOIN Product p on p.id = t1.product
GROUP BY p.id;

| id  | sumexpenseitems | sumincomeitems |
| --- | --------------- | -------------- |
| 1   | 125             | 125            |
| 2   | 180             | 180            |
| 3   | 180             | 180            |

View on DB Fiddle

Upvotes: 3

Related Questions