Pratik
Pratik

Reputation: 982

How to get data from mysql for given date range group by weekly,monthly

I have a table which has data in this format:

id name cnt1 created_on
'1','uac','116','2014-09-06 17:16:29'
'2','uac','116','2014-09-06 17:17:06'
'3','uac','90','2014-09-06 21:53:34'
'4','uac','100','2014-08-06 21:53:34'
'5','uac','1','2014-07-06 21:53:34'
'6','uac','2','2014-07-26 21:53:34'
'7','uac','3','2014-09-01 21:53:34'
'8','uac','4','2014-09-02 21:53:34'
'9','uac','5','2014-09-03 21:53:34'
'10','uac','6','2014-09-04 21:53:34'
'11','uac','7','2014-09-05 21:53:34'
'12','uac','8','2014-09-07 21:53:34'
'13','uac','9','2014-09-08 21:53:34'

I want data for given date range should be grouped on 1. Weekly 2. Monthly Also I want that for a week or month I should get data for last dat of week or month. Eg if I am getting data for monthly I should get following output:

'6','uac','1','2014-07-26 21:53:34'
'4','uac','100','2014-08-06 21:53:34'
'13','uac','116','2014-09-08 21:53:34'

I tried this query

SELECT id,name,cnt1,created_on
FROM qa_dashboard.project_qa_coverage_detail
GROUP BY year(created_on), month(created_on);

but this is giving me following output

'5','uac','1','2014-07-06 21:53:34'
'4','uac','100','2014-08-06 21:53:34'
'1','uac','116','2014-09-06 17:16:29'

Please help

Upvotes: 0

Views: 763

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You don't actually want a group by query. You want to get the last row for each set. Here is a method using not exists:

SELECT cd.*
FROM qa_dashboard.project_qa_coverage_detail cd
WHERE NOT EXISTS (SELECT 1
                  FROM qa_dashboard.project_qa_coverage_detail cd2
                  WHERE year(cd2.created_on) = year(cd.created_on) and
                        month(cd2.created_on) = month(cd.created_on) and
                        cd2.created_on > cd.created_on
                 ) ;

This is saying, in essence: "Get me all rows from the table where there is no other row with the same year and month and a more recent created_on date." That is a fancy way of saying "Get me the last row for each month."

EDIT;

If you want the values from the first and last date of the month, then use a join method instead:

select cd.*, cdsum.minco as first_created_on
from qa_dashboard.project_qa_coverage_detail cd join
     (select year(cd2.created_on) as yr, month(cd2.created_on) as mon,
             min(cd2.created_on) as minco, max(cd2.created_on) as maxco
      from qa_dashboard.project_qa_coverage_detail cd2
      group by year(cd2.created_on), month(cd2.created_on)
     ) cdsum
     on cd.created_on = cd2.maxco;

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

Pretty sure this will get you your expected output:

Last for month:

select t.*
  from tbl t
  join (select max(created_on) as last_for_month
          from tbl
         group by year(created_on), month(created_on)) v
    on t.created_on = v.last_for_month

Except where you say you expect:

'6','uac','1','2014-07-26 21:53:34'

I think what you really want is:

'6','uac','2','2014-07-26 21:53:34'

(based on the sample data you provided)

Fiddle: http://sqlfiddle.com/#!9/faaa3/4/0

Last for week:

select t.*
  from tbl t
  join (select max(created_on) as last_for_week
          from tbl
         group by year(created_on), week(created_on)) v
    on t.created_on = v.last_for_week

Based on your comment, if you want the last value and the last id for the last of the month, but the value of cnt1 for the first of the month, use the following (change month() to week() if you want the same but for week):

select v.id, v2.first_created_on, v.cnt1
  from (select t.id, t.created_on, t.cnt1
          from tbl t
          join (select max(created_on) as last_created_on
                 from tbl
                group by year(created_on), month(created_on)) v
            on t.created_on = v.last_created_on) v
  join (select min(created_on) as first_created_on
          from tbl
         group by year(created_on), month(created_on)) v2
    on year(v.created_on) = year(v2.first_created_on)
   and month(v.created_on) = month(v2.first_created_on)

Fiddle: http://sqlfiddle.com/#!9/faaa3/5/0

Output:

| ID |                 FIRST_CREATED_ON | CNT1 |
|----|----------------------------------|------|
|  4 |    August, 06 2014 21:53:34+0000 |  100 |
|  6 |      July, 06 2014 21:53:34+0000 |    2 |
| 13 | September, 01 2014 21:53:34+0000 |    9 |

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

That looks like the expected output, one row per year and month. You've specified created_on as an expression in the SELECT list, so you are getting the value from the created_on column from one row in the each group.

If you want just year and month in the output, you'd need to use a different expression in the SELECT list. For example,

DATE_FORMAT(created_on,'%Y-%m') AS yyyy_mm 

You could use the same expression the GROUP BY clause, rather that two separate expressions.

As another option, you could use the YEAR(created_on), MONTH(created_on) in the SELECT list.


To get rows "grouped" by week, you could use WEEK(created_on) in place of MONTH(created_on).


To return the "last" row for each group, the normal pattern is to use an inline view and a JOIN operation. For example:

SELECT t.id
     , t.project
     , t.total_tc
     , t.created_on
  FROM qa_dashboard.project_qa_coverage_detail t
  JOIN ( SELECT MAX(r.created_on)
           FROM qa_dashboard.project_qa_coverage_detail r
          GROUP BY DATE_FORMAT(r.created_on,'%Y-%m')
       ) s
    ON s.created_on = t.created

Note that if there are two (or more) rows with the same created_on value, there's a potential for this query to return more than one row for the group.

Upvotes: 0

Related Questions