Group query results by month and year in postgresql

Question

I have the following database table on a Postgres server    id      date          Product Sales 1245    01 04 2013    Toys    1000      1245    01 04 2013    Toys    2000 1231    01 02 2013    Bicycle 50000 456461  01 01 2014    Bananas 4546   I would like to create a query that gives the SUM of the Sales column and groups the results by month and year as follows   Apr    2013    3000     Toys Feb    2013    50000    Bicycle Jan    2014    4546     Bananas   Is there a simple way to do that

User · Accepted Answer

select to char date  Mon   as mon         extract year from date  as yyyy         sum  Sales   as  Sales  from yourtable group by 1 2   At the request of Radu  I will explain that query   to char date  Mon   as mon    converts the  date  attribute into the defined format of the short form of month   extract year from date  as yyyy   Postgresql s  extract  function is used to extract the YYYY year from the  date  attribute   sum  Sales   as  Sales    The SUM   function adds up all the  Sales  values  and supplies a case-sensitive alias  with the case sensitivity maintained by using double-quotes   group by 1 2   The GROUP BY function must contain all columns from the SELECT list that are not part of the aggregate  aka  all columns not inside SUM AVG MIN MAX etc functions   This tells the query that the SUM   should be applied for each unique combination of columns  which in this case are the month and year columns  The  1 2  part is a shorthand instead of using the column aliases  though it is probably best to use the full  to char       and  extract       expressions for readability

User · Answer

Take a look at example E of this tutorial -  https   www postgresqltutorial com postgresql-group-by   You need to call the function on your GROUP BY instead of calling the name of the virtual attribute you created on select   I was doing what all the answers above recommended and I was getting a column  year month  does not exist error    What worked for me was    SELECT      date trunc  month   created at    MM YYYY  AS month FROM       orders    GROUP BY      date trunc  month   created at

User · Answer

I can t believe the accepted answer has so many upvotes -- it s a horrible method   Here s the correct way to do it  with date trunc      SELECT date trunc  month   txn date  AS txn month  sum amount  as monthly sum      FROM yourtable  GROUP BY txn month   It s bad practice but you might be forgiven if you use   GROUP BY 1   in a very simple query    You can also use   GROUP BY date trunc  month   txn date    if you don t want to select the date

User · Answer

bma answer is great  I have used it with ActiveRecords  here it is if anybody needs it in Rails   Model find by sql     SELECT TO CHAR created at   Mon   AS month     EXTRACT year from created at  as year     SUM desired value  as desired value    FROM desired table    GROUP BY 1 2    ORDER BY 1 2

User · Answer

There is another way to achieve the result using the date part   function in postgres    SELECT date part  month   txn date  AS txn month  date part  year   txn date  AS txn year  sum amount  as monthly sum      FROM yourtable  GROUP BY date part  month   txn date    Thanks

User · Answer

Postgres has few types of timestamps   timestamp without timezone -  Preferable to store UTC timestamps  You find it in multinational database storage  The client in this case will take care of the timezone offset for each country   timestamp with timezone - The timezone offset is already included in the timestamp   In some cases  your database does not use the timezone but you still need to group records in respect with local timezone and Daylight Saving Time  e g  https   www timeanddate com time zone romania bucharest   To add timezone you can use this example and replace the timezone offset with yours    your date column  at time zone   03    To add the  1 Summer Time offset specific to DST you need to check if your timestamp falls into a Summer DST  As those intervals varies with 1 or 2 days  I will use an aproximation that does not affect the end of month records  so in this case i can ignore each year exact interval   If more precise query has to be build  then you have to add conditions to create more cases  But roughly  this will work fine in splitting data per month in respect with timezone and SummerTime when you find timestamp without timezone in your database   SELECT       id    Product    Sale       date trunc  month            CASE WHEN              Extract month from t  date    gt  03 AND             Extract day from t  date    gt  26 AND             Extract hour from t  date    gt  3 AND             Extract month from t  date    lt  10 AND             Extract day from t  date    lt  29 AND             Extract hour from t  date    lt  4         THEN              t  date  at time zone   03  -- Romania TimeZone offset   DST         ELSE             t  date  at time zone   02  -- Romania TimeZone offset          END  as  date  FROM      public  Table  AS t WHERE 1 1     AND t  date   gt    01 07 2015 00 00 00   TIMESTAMP WITHOUT TIME ZONE     AND t  date   lt   01 07 2017 00 00 00   TIMESTAMP WITHOUT TIME ZONE GROUP BY date trunc  month        CASE WHEN          Extract month from t  date    gt  03 AND         Extract day from t  date    gt  26 AND         Extract hour from t  date    gt  3 AND         Extract month from t  date    lt  10 AND         Extract day from t  date    lt  29 AND         Extract hour from t  date    lt  4     THEN          t  date  at time zone   03  -- Romania TimeZone offset   DST     ELSE         t  date  at time zone   02  -- Romania TimeZone offset      END

User · Answer

to char actually lets you pull out the Year and month in one fell swoop   select to char date  2014-05-10    Mon-YY   as year month  -- May-14  select to char date  2014-05-10    YYYY-MM   as year month  -- 2014-05    or in the case of the user s example above   select to char date  YY-Mon   as year month        sum  Sales   as  Sales  from some table group by 1

[sql] Group query results by month and year in postgresql

Examples related to sql

Examples related to postgresql