Untitled

mail@pastecode.io avatar
unknown
sql
a year ago
1.2 kB
1
Indexable
Never
WITH 

Y11 AS (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
        COUNT(invoice_id) AS year_2011,
        EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) AS year_2011
        FROM invoice
        WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2011
        GROUP BY invoice_month),

Y12 AS (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
        COUNT(invoice_id) AS year_2012,
        EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) AS year_2012
        FROM invoice
        WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2012
        GROUP BY invoice_month) AS Y12 ON Y11.invoice_month = Y12.invoice_month),

Y13 AS (SELECT EXTRACT(MONTH FROM CAST(invoice_date AS DATE)) AS invoice_month,
        COUNT(invoice_id) AS year_2013,
        EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) AS year_2012,
        FROM invoice
        WHERE EXTRACT(YEAR FROM CAST(invoice_date AS DATE)) = 2013
        GROUP BY invoice_month) AS Y13 ON Y11.invoice_month = Y13.invoice_month)

SELECT Y11.invoice_month as invoice_month ,
       Y11.year_2011 AS year_2011,
       Y12.year_2012 AS year_2012,
       Y13.year_2013 AS year_2013