Show column name as months from past year starting from current_date

Question

I am writing a report that shows the total amount of codes for each month since the past year.

Currently if I just do a count for all of the codes in the past year then my result set will look like this

name    | code  | total   |  date
build1    x1      10        04-2013
build1    x50     60        05-2013
build1    x1      80        06-2013
build1    x90     450       07-2013

I was able to transpose all of rows so all of the columns would be the month, with the total below it. My updated results look like this now

name    |  code | apl |  may | jun | jul
build1     x1      10    0     80     0
build1     x50     0     60     0     0
build1     x90     0     0      0     450

The code above are the results i am looking for but what I am wanting to do now is to order everything by the current month and then back one year from that current month.

So if the current month is july then my result set would be ordered like this

name    |  code | jul |  jun | may | apl
build1     x1      0     80     0     10
build1     x50     0      0     60     0
build1     x90     450    0     0      0

The problem I am running in to that that I am using alias's as the month names. And you cannot grab the month from an alias. Also, alias's as far as I know are static so they can't change once you have them set. The only way to get the month as a column name is to extract it from your data set. But when I transpose the rows into column I have to use alias's since I am using case statements to get all of the totals for each month.

EDIT: Sorry, postgresql version is 8.4, here is my query that i have so far

SELECT 


pname,
code,
SUM(totaljanurary)  AS "Janurary",
SUM(totalfebruary)  AS "February",
SUM(totalmarch)     AS "March",
SUM(totalapril)     AS "April",
SUM(totalmay)       AS "May",
SUM(totaljune)      AS "June",
SUM(totaljuly)      AS "July",
SUM(totalaugust)    AS "August",
SUM(totalseptember) AS "September",
SUM(totaloctober)   AS "October",
SUM(totalnovember)  AS "November",
SUM(totaldecember)  AS "December"

FROM(

SELECT 

    pname,
    code,
    SUM(case when extract (month FROM checked_date)=01 then total else 0 end) AS totaljanurary,
    SUM(case when extract (month FROM checked_date)=02 then total else 0 end) AS totalfebruary,
    SUM(case when extract (month FROM checked_date)=03 then total else 0 end) AS totalmarch,
    SUM(case when extract (month FROM checked_date)=04 then total else 0 end) AS totalapril,
    SUM(case when extract (month FROM checked_date)=05 then total else 0 end) AS totalmay,
    SUM(case when extract (month FROM checked_date)=06 then total else 0 end) AS totaljune,
    SUM(case when extract (month FROM checked_date)=07 then total else 0 end) AS totaljuly,
    SUM(case when extract (month FROM checked_date)=08 then total else 0 end) AS totalaugust,
    SUM(case when extract (month FROM checked_date)=09 then total else 0 end) AS totalseptember,
    SUM(case when extract (month FROM checked_date)=10 then total else 0 end) AS totaloctober,
    SUM(case when extract (month FROM checked_date)=11 then total else 0 end) AS totalnovember,
    SUM(case when extract (month FROM checked_date)=12 then total else 0 end) AS totaldecember



    FROM (

        --START HERE
        SELECT

            pname,
            code,
            COUNT(code)AS total,
            date_trunc('month',checked_date)::date AS checked_date


            FROM table1

            AND checked_date >= current_date-365
            AND checked_date <= current_date


            GROUP BY pname, code, date_trunc('month',checked_date)
    )T1
    GROUP BY pname, code, date_trunc('month',checked_date)

)T2
GROUP BY pname, code
ORDER BY pname, code

You forgot to provide the Postgres version, your table definition, your current query (working or not) and some sample data. — Erwin Brandstetter, Jul 09 '13 at 16:10

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

You want crosstab(), provided by the additional module tablefunc.

Assuming "date" is of type date (like it should be).
Other details depend on details you forgot to provide.

SELECT * FROM crosstab(
     $$SELECT name, code, to_char("date", 'mon'), total
       FROM   tbl
       WHERE  "date" <  now()
       AND    "date" >= now() - interval '1 year'
       ORDER  BY name, extract(month from now()) DESC$$

    ,$$VALUES
      ('dec'::text), ('nov'), ('oct'), ('sep'), ('aug'), ('jul')
    , ('jun'),       ('may'), ('apr'), ('mar'), ('feb'), ('jan')$$
   )
AS ct (name text, code text
   , dec int, nov int, oct int, sep int, aug int, jul int
   , jun int, may int, apr int, mar int, feb int, jan int);

Refer to this closely related answer for additional instructions:
Sum by month and put months as columns

You shouldn't be using date as identifier, it's a reserved word. I double-quoted it.
You shouldn't be using the non-descriptive name name either.

edited May 23 '17 at 11:49

Community

1
1

answered Jul 09 '13 at 16:21

Erwin Brandstetter

605,456
145
1,078
1,228

I tried using crosstab before but it didn't work. I included my query above to show you how I used the case statements. Even if I would've used the crosstab it still isn't what I am looking for. Those columns are still static, I am looking for a rolling months report. So the current month will be displayed first followed by the other months. This would change as the month changes too. – norski_lab Jul 09 '13 at 17:13
@norski_lab: You still forgot the table definition ( `\d tbl` in psql; include relevant columns with their data types) and sample data. crosstab() should work just fine. Dynamic columns are hard to come by. My example delivers `NULL` for months that have no value. – Erwin Brandstetter Jul 09 '13 at 18:50

Show column name as months from past year starting from current_date

1 Answers1