fetching monthwise data from a database with a year's record in postgres

Question

I am having a table with a years data with the following columns:

Table "myData" ((
    "Status" character varying,  
    "Project" character varying,  
    "Product" character varying, 
    "Identifier" character varying, 
    "Submittedon" date
    )

etc.,

Now to fetch a count of records submitted on a particular month. Say like April 2013's Record count, I am using:

select count("Status") as April2013 
from "myData"  
where (
    "SubmittedOn" > (current_date - 90) 
    and "SubmittedOn" < (current_date - 60)
)

Result:

April2013
--------
62

Now my requirement is to fetch the count of records for the past 6 months. I mean i want my output in any of the below formats:

FORMAT 1:

enter image description here

FORMAT 2:

6MonthsCount
-------------
34

23

44

41

18

9

You tagged this both mysql and postgresql, which system do you use? — lathspell
– lathspell, Commented Jun 12, 2013 at 10:48

Clodoaldo Neto · Accepted Answer · 2013-06-12 11:19:18Z

2

select
    date_trunc('month', submittedOn) "month",
    count("Status") total
from "myData"  
group by 1
order by 1

answered Jun 12, 2013 at 11:19

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Deepak Over a year ago

@Clodaoldo Neto: Thanks..I am looking into your suggestion!!

Deepak Over a year ago

@Clodaoldo Neto: Thank you once again, your solution helped me fix my problem!! :)

Deepak Over a year ago

@Clodaoldo Neto: How to get the value as 0 for a month. When there is no entry for submittedOn column for a particular month..Right now..The month and value wont come...

Clodoaldo Neto Over a year ago

@Deepak Check this related answer or post another question.

Craig Ringer · Accepted Answer · 2013-06-12 11:58:49Z

2

This looks like a "Pivot"-Table so use the crosstab() function of the tablefunc extention (http://www.postgresql.org/docs/current/static/tablefunc.html):

CREATE TABLE mydata (status text, submitteton date);
INSERT INTO mydata VALUES ('a', '2013-01-02'), ('b', '2013-01-05'), ('c', '2013-02-09'), ('d', '2013-04-11');


SELECT extract(month from submitteton) as month, count(*) FROM mydata GROUP BY month;
 month | count 
-------+-------
     1 |     2
     2 |     1
     4 |     1

CREATE EXTENSION tablefunc;
SELECT 
  *
FROM 
  crosstab(
    'SELECT 
       extract(year from submitteton)::int as year,
       extract(month from submitteton) as month, 
       count(*)::int 
     FROM
       mydata 
     GROUP BY 1,2 
     ORDER BY 1,2', 

     'SELECT * FROM generate_series(1, 12)'
  ) as ct(
    year int,
    jan int, feb int, mar int, apr int, may int, jun int, 
    jul int, aug int, sep int, oct int, nov int, dec int
  )
ORDER BY 
  year
;

 year | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec 
------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 2013 |   2 |   1 |     |   1 |     |     |     |     |     |     |     |

edited Jun 12, 2013 at 11:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

answered Jun 12, 2013 at 11:17

lathspell

3,3201 gold badge37 silver badges55 bronze badges

2 Comments

Deepak Over a year ago

Thanks for your help. How to add one more filter to the count, I mean I do not want the entire count of records, I want records whose Status = "XYZ". Once again thanks for your help!! :)

lathspell Over a year ago

I guess you can add a WHERE after the "FROM mydata"

Denis de Bernardy · Accepted Answer · 2013-06-12 11:03:08Z

You ought to use intervals and date_trunc, rather than explicit numbers of days:

denis=# select date_trunc('month', now()) as d;
           d            
------------------------
 2013-06-01 00:00:00+02
(1 row)

denis=# select date_trunc('month', now()) - interval '2 months' as d;
           d            
------------------------
 2013-04-01 00:00:00+02
(1 row)

To generate the past 6 months, use generate_series():

denis=# select d as start, d + interval '1 month' as stop
        from generate_series(date_trunc('month', now()) - interval '6 month',
                             date_trunc('month', now()),
                             '1 month') d;
         start          |          stop          
------------------------+------------------------
 2012-12-01 00:00:00+01 | 2013-01-01 00:00:00+01
 2013-01-01 00:00:00+01 | 2013-02-01 00:00:00+01
 2013-02-01 00:00:00+01 | 2013-03-01 00:00:00+01
 2013-03-01 00:00:00+01 | 2013-04-01 00:00:00+02
 2013-04-01 00:00:00+02 | 2013-05-01 00:00:00+02
 2013-05-01 00:00:00+02 | 2013-06-01 00:00:00+02
 2013-06-01 00:00:00+02 | 2013-07-01 00:00:00+02
(7 rows)

http://www.postgresql.org/docs/current/static/functions-srf.html

From there, a simple join with a count/group by will yield the expected result.

(Note: if you need timestamps without a timezone, replace now() with e.g. (now() at time zone 'utc')::timestamp(0)

Collectives™ on Stack Overflow

fetching monthwise data from a database with a year's record in postgres

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related