Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT a.billing_country country,
- a.total_invoice total_invoice,
- b.total_customer
- FROM
- (SELECT billing_country ,
- COUNT(invoice_id) total_invoice
- FROM invoice
- WHERE EXTRACT (YEAR FROM CAST (invoice_date AS date)) in (SELECT
- i.year
- FROM
- (SELECT
- EXTRACT (YEAR FROM CAST (invoice_date AS date)) AS year,
- SUM(total) sum
- FROM invoice
- WHERE EXTRACT(MONTH FROM CAST (invoice_date AS date)) IN (6,7,8)
- GROUP BY year
- ORDER BY sum DESC
- LIMIT 1) AS i)
- GROUP BY billing_country) AS a
- LEFT OUTER JOIN
- (SELECT i.billing_country,
- COUNT(i.customer_id) total_customer
- FROM client AS c
- LEFT OUTER JOIN invoice AS i ON c.customer_id = i.customer_id
- GROUP BY billing_country) AS b ON a.billing_country = b.billing_country
- ORDER BY total_invoice DESC, country
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement