Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT a.billing_country,
- a.total_invoice,
- b.total_customer
- FROM
- (SELECT billing_country,
- COUNT(invoice_id) AS total_invoice
- FROM
- (SELECT
- EXTRACT (YEAR FROM CAST (invoice_date AS date)) AS year,
- billing_country,
- invoice_id
- FROM invoice) AS i
- RIGHT OUTER JOIN
- (SELECT
- EXTRACT (YEAR FROM CAST (invoice_date AS date)) AS year,
- SUM (total) AS 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 k ON i.year=k.year
- GROUP BY billing_country) AS a
- LEFT OUTER JOIN
- (SELECT
- i.billing_country billing_country,
- COUNT(c.customer_id) total_customer
- FROM invoice AS i
- LEFT OUTER JOIN client AS c ON i.customer_id = c.customer_id
- GROUP BY billing_country) as b ON a.billing_country = b.billing_country
- ORDER BY total_invoice DESC,billing_country
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement