Re: Re: Re: Untitled

From Tinct Agouti, 11 Months ago, written in SQL, viewed 230 times. This paste is a reply to Re: Re: Untitled from Commodious Monkey - view diff
URL http://codebin.org/view/ecc2ef0b Embed
Download Paste or View Raw
  1. SELECT i.billing_country AS country,
  2.        COUNT(total) AS total_invoice,
  3.        COUNT(DISTINCT customer_id) AS total_customer
  4. FROM invoice AS i
  5. LEFT JOIN
  6. (
  7.     SELECT billing_country,
  8.            COUNT(total) AS total_invoice
  9.     FROM invoice
  10.     GROUP BY billing_country
  11. ) AS tig ON i.billing_country = tig.billing_country
  12. LEFT JOIN
  13. (
  14.     SELECT country,
  15.     COUNT(DISTINCT customer_id) AS total_customer
  16.     FROM client
  17.     GROUP BY country
  18. ) AS tcg ON tig.billing_country = tcg.country
  19. WHERE EXTRACT (YEAR FROM CAST (invoice_date AS DATE)) IN
  20. (
  21.     SELECT EXTRACT (YEAR FROM CAST (invoice_date AS DATE)) AS YEAR
  22.     FROM invoice
  23.     WHERE EXTRACT (MONTH FROM CAST (invoice_date AS DATE)) IN (6,7,8)
  24.     GROUP BY YEAR
  25.     ORDER BY SUM(total) DESC
  26.     LIMIT 1
  27. )
  28. GROUP BY i.billing_country
  29. ORDER BY total_invoice DESC, country

Reply to "Re: Re: Re: Untitled"

Here you can reply to the paste above