CODEWITHSUNDEEP
sqlpostgresql
Hector Leon
Hector Leon

Reputation: 15

Query for count the total number of orders and sent orders per customer (return 0 if none)

I have two tables Order with columns:

orderId(int), name(varchar), customerId(int), sent(bool), deleted(bool)

And Customer with columns:

customerId(int), name(varchar), address(varchar), deleted(bool)

I need to return all Customer columns, with additional TotalOrders count and TotalSentOrders count columns (for each customer) for the Customers and Orders that deleted = false and count 0 if there are no orders or sent orders.

Expected Outcome:

|customerId|name|address|deleted|TotalOrders|TotalSentOrders|
|     1    |John| A St. | false |     0     |       0       |
|     2    |Jack| B St. | false |     5     |       1       |
|     3    |Luke| C St. | false |     4     |       0       |

I'm doing this in PostgreSQL 9.6

Upvotes: 1

Views: 884

Answers (1)

S-Man
S-Man

Reputation: 23756

demo:db<>fiddle

SELECT
    c.*,
    COUNT(*) FILTER (WHERE o.orderId IS NOT NULL),    -- 3
    COUNT(*) FILTER (WHERE o.sent = true)             -- 4
FROM customer c
LEFT JOIN orders o ON c.customerId = o.customerId     -- 1
WHERE c.deleted = false AND o.deleted = false         -- 2
GROUP BY c.customerId, c.name, c.address, c.deleted
  1. LEFT JOIN both tables, to keep Johns data
  2. Remove all deleted
  3. GROUP BY customer and COUNT the remaining records. Because John is still in the result set, he would be counted as well. To ensure you only count the orders you could use the FILTER clause to adjust the aggregate function.
  4. Use the FILTER clause once more to COUNT only the sent orders.

Upvotes: 1

Related Questions