CODEWITHSUNDEEP
sqlite
Sneedlewoods
Sneedlewoods

Reputation: 23

How to join these two tables properly?

I have these two tables

customers:

id | name                | address
---+---------------------+-------------------------
1  | company 1           | some address information
2  | company 2           | another address
3  | yet another company | no address here

orders:

id | customer_id | date
---+-------------+---------
1  | 2           | 20151209
2  | 2           | 20151211
3  | 3           | 20151210
4  | 1           | 20151223

Now I want to get a resulting table with each customer on the left and the amount of orders within an arbitrary period of time on the right. For example, given this period to be 20151207 <= date <= 20151211, the resulting table should look like this:

name                | orders count
--------------------+-------------
company 1           | 0
company 2           | 2
yet another company | 1

Note: date = 20151207 means the 7th of december 2015.

How to join them?

Upvotes: 2

Views: 65

Answers (1)

Fallso
Fallso

Reputation: 1301

SELECT c.name, COUNT(CASE WHEN ((o.date BETWEEN 20151207 AND 20151211) OR (o.date ISNULL)) THEN o.customer_id END) AS "Total Sales" FROM customers AS c LEFT JOIN orders o ON c.id == o.customer_id GROUP BY c.name

Upvotes: 1

Related Questions