Advertisement
deniswhite77

Untitled

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