Advertisement
deniswhite77

Untitled

Feb 13th, 2022
220
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT a.billing_country country,
  2.     a.total_invoice total_invoice,
  3.     b.total_customer
  4. FROM
  5. (SELECT billing_country ,
  6.    COUNT(invoice_id) total_invoice
  7. FROM invoice
  8. WHERE EXTRACT (YEAR FROM CAST (invoice_date AS date)) in (SELECT
  9.                                                                 i.year
  10.                                                           FROM
  11.                                                          (SELECT
  12.                                                                EXTRACT (YEAR FROM CAST (invoice_date AS date)) AS year,
  13.                                                                SUM(total) sum
  14.                                                           FROM invoice
  15.                                                           WHERE EXTRACT(MONTH FROM CAST (invoice_date AS date)) IN (6,7,8)
  16.                                                           GROUP BY year
  17.                                                           ORDER BY sum DESC
  18.                                                           LIMIT 1) AS i)
  19.                                                           GROUP BY billing_country) AS a
  20. LEFT OUTER JOIN
  21. (SELECT i.billing_country,
  22.     COUNT(i.customer_id) total_customer
  23. FROM client AS c
  24. LEFT OUTER JOIN invoice AS i ON c.customer_id = i.customer_id
  25. GROUP BY billing_country) AS b ON a.billing_country = b.billing_country
  26. ORDER BY total_invoice DESC, country
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement