Guest User

Untitled

a guest
Jan 23rd, 2019
67
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.73 KB | None | 0 0
  1. <<customers>>
  2. id | first_name | last_name
  3. 1 | Reed | Richards
  4. 2 | Johnny | Storm
  5. 3 | Peter | Parker
  6.  
  7. <<purchases>>
  8. id | cid | date
  9. 1 | 1 | 2017-01-09
  10. 2 | 2 | 2017-01-09
  11. 3 | 2 | 2017-01-09
  12. 4 | 3 | 2017-01-09
  13.  
  14. SELECT
  15. COUNT(c.id) as "Total Customers",
  16. COUNT(p.id) as "Total Sales",
  17. COUNT(c.id)/COUNT(p.id) as "Sales per customer"
  18. FROM test_customers c
  19. LEFT OUTER JOIN test_purchases p ON c.id = p.cid
  20.  
  21. 4 | 4 | 1
  22.  
  23. 3 | 4 | 1.3333333...
  24.  
  25. SELECT
  26. COUNT(distinct c.id) as "Total Customers",
  27. COUNT(distinct p.id) as "Total Sales",
  28. COUNT(distinct c.id) * 1.00 / COUNT(distinct p.id) as "Sales per customer"
  29. FROM test_customers c
  30. LEFT OUTER JOIN test_purchases p ON c.id = p.cid
Add Comment
Please, Sign In to add comment