Reputation: 982
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
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
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
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