Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <<customers>>
- id | first_name | last_name
- 1 | Reed | Richards
- 2 | Johnny | Storm
- 3 | Peter | Parker
- <<purchases>>
- id | cid | date
- 1 | 1 | 2017-01-09
- 2 | 2 | 2017-01-09
- 3 | 2 | 2017-01-09
- 4 | 3 | 2017-01-09
- SELECT
- COUNT(c.id) as "Total Customers",
- COUNT(p.id) as "Total Sales",
- COUNT(c.id)/COUNT(p.id) as "Sales per customer"
- FROM test_customers c
- LEFT OUTER JOIN test_purchases p ON c.id = p.cid
- 4 | 4 | 1
- 3 | 4 | 1.3333333...
- SELECT
- COUNT(distinct c.id) as "Total Customers",
- COUNT(distinct p.id) as "Total Sales",
- COUNT(distinct c.id) * 1.00 / COUNT(distinct p.id) as "Sales per customer"
- FROM test_customers c
- LEFT OUTER JOIN test_purchases p ON c.id = p.cid
Add Comment
Please, Sign In to add comment