Postgres join and count multiple relational tables

Question

I want to join the 2 tables to the first table and group by a vendor name. I have three tables listed below.

Vendors Table

| id         |      name 
|:-----------|------------:|
| test-id    | Vendor Name |

VendorOrders Table

| id         | VendorId    | Details      | isActive(Boolean)| price |
|:-----------|------------:|:------------:| -----------------| --------
| random-id  |   test-id   |  Sample test |   TRUE            | 5000

OrdersIssues Table

| id         | VendorOrderId| Details.   |
|:-----------|--------------:-----------:|
| order-id   |   random-id  | Sample test|

The expected output is to count how many orders belong to a vendor and how many issues belongs to a vendor order.

I have the below code but it's not giving the right output.

SELECT "vendors"."name" as "vendorName", 
COUNT("vendorOrders".id) as allOrders,
COUNT("orderIssues".id) as allIssues 
FROM "vendors" 
LEFT OUTER JOIN "vendorOrders" ON "vendors".id = "vendorOrders"."vendorId" 
LEFT OUTER JOIN "orderIssues" ON "orderIssues"."vendorOrderId" = "vendorOrders"."id"
GROUP BY "vendors".id;```

forpas · Accepted Answer · 2020-09-30 13:54:55Z

1

You need the keyword DISTINCT, at least for allOrders:

SELECT v.name vendorName, 
       COUNT(DISTINCT vo.id) allOrders,
       COUNT(DISTINCT oi.id) allIssues 
FROM vendors v
LEFT OUTER JOIN vendorOrders vo ON v.id = vo.vendorId 
LEFT OUTER JOIN orderIssues oi ON oi.vendorOrderId = vo.id
GROUP BY v.id, v.name;

Consider using aliases instead of full table names to make the code shorter and more readable.

edited Sep 30, 2020 at 13:54

answered Sep 30, 2020 at 13:31

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dealwap Over a year ago

Thanks for your response, any idea how I can also count orders that isActive=true to use the alias "activeOrders"?

forpas Over a year ago

@dealwap Try: COUNT(DISTINCT CASE WHEN vo.isActive THEN vo.id END) activeOrders.

dealwap Over a year ago

On this same table, what if I need to sum up a price column? note that I can't use DISTINCT because different table might have the same values. Any idea?

forpas Over a year ago

In a query with multiple joins the result may contain duplicate rows for the price that you want to sum. So just SUM(price) may return the wrong result. Post a question with sample data and expected results to clarify what you want.

Gordon Linoff · Accepted Answer · 2020-09-30 13:43:50Z

You are joining along two related dimensions. The overall number of rows is the number of issues. But to get the number of orders, you need a distinct count:

SELECT v.*, count(distinct vo.id) as num_orders,
       COUNT(oi.vendororderid) as num_issues
FROM vendors v LEFT JOIN
     vendorOrders vo
     ON v.id = vo.vendorId LEFT JOIN
     orderIssues oi
     ON oi.vendorOrderId = vo.id
GROUP BY v.id;

Notes:

Table aliases make the query easier to write and to read.
Quoting column and table names makes the query harder to write and read. Don't quote identifiers (you may need to recreate the tables).
Postgres support SELECT v.* . . . GROUP BY v.id assuming that the id is the primary key (actually, it only needs to be unique). This seems like a reasonable assumption.

Collectives™ on Stack Overflow

Postgres join and count multiple relational tables

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related