-1

I have table where i have some data and i am doing a group by operation on those data set. I.I am doing the group by operation using date , like i have my first and last column as date so my first column is date and last column is previous Sunday of that date,so which dates have same previous Sunday will belong to a group .So i am doing the group by by previous Sunday .Now my requirement is ,when i am doing group by i have need to show starting date and ending date of that group i am column. I am posting my table structure below ,

 DateAdded  Souring Inake placement  PreviousSunday 
'22 Jan 2015'   2     4       6      '2015-01-18'
'23 Jan 2015'   5     6       8      '2015-01-18'
'24 Jan 2015'   8     7       6      '2015-01-18'
'26 Jan 2015'   6     1       9      '2015-01-25'
'27 Jan 2015'   8     56      0      '2015-01-25'

........................................... and so on . so my 1st 3 rows have their previous Sunday common,so they belong o a group.next two also have their previous Sunday common.so In my output what i want to show like 

DateAdded  Souring Inake placement  PreviousSunday DateStartEnd 
'22 Jan 2015'   2     4       6      '2015-01-18'  '22 Jan 2015' - '24 Jan 2015'  
'26 Jan 2015'   6     1       9      '2015-01-25'  '26 Jan 2015' - '27 Jan 2015'

How to achieve that ?? someone please help

lucifer
  • 2,297
  • 18
  • 58
  • 100

3 Answers3

0

You can get what you want by converting the dateAdded value to a real date and then using min() and max():

select min(dateAdded), min(souring), min(inake), min(placement), previousSunday
       concat(min(str_to_date(dateAdded, '%d %b %Y)), '-', min(str_to_date(dateAdded, '%d %b %Y)))
from table t
group by previousSunday;

This does change the format (you can use date_format() to control that). The more important question is why you are storing dates as strings in your database. You should use the appropriate type.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Not sure about the use of `MIN()` here. Using `MIN()`, `placement` will be `0` for 26 Jan 2015, and not `9` as shown in the question. – eillarra Apr 04 '15 at 20:12
  • `select min(str_to_date(DateAdded, '%d %b %Y')) as DateAdded, Souring, Inake, placement, PreviousSunday, concat(min(str_to_date(dateAdded, '%d %b %Y')), ' => ', max(str_to_date(DateAdded, '%d %b %Y'))) as DateStartEnd from t group by PreviousSunday` will be a corrected version of your query, that, by the way, looks cleaner than mine :) – eillarra Apr 04 '15 at 20:19
0

Something like this should work, assuming that your DateAdded is a date and you can rely on it for ordering or for using MAX():

select s.DateAdded, s.Souring, s.Inake, s.placement, s.PreviousSunday, CONCAT(s.DateAdded, ' => ', e.DateEnd) as DateStartEnd
from table_name as s 
inner join (select MAX(DateAdded) as DateEnd, PreviousSunday from table_name group by PreviousSunday) as e on s.PreviousSunday = e.PreviousSunday
group by e.DateEnd
order by s.DateAdded

If DateAdded is a string (why?) you should combine this query with STR_TO_DATE() like it is shown here.

Community
  • 1
  • 1
eillarra
  • 5,027
  • 1
  • 26
  • 32
  • @ eillarra I don't want to show the previous sunday in the output i want to show the start index and the end index of the group see my required output – lucifer Apr 05 '15 at 03:59
0

remember to replace instances of mytable with your actual table name. I have assumed that the columns are named after what you provided.

SELECT MIN(DateAdded), 
(SELECT Souring FROM mytable WHERE DateAdded = mt.DateAdded) AS Souring ,
(SELECT Inake FROM mytable WHERE DateAdded = mt.DateAdded) AS Inake, 
(SELECT Placement FROM mytable WHERE DateAdded = mt.DateAdded) AS     Placement,
MAX(PreviousSunday), Concat(DateAdded, " - ", MAX(DateAdded))     DateStartEnd
FROM mytable mt GROUP BY PreviousSunday
zana
  • 304
  • 1
  • 6
  • If this is the case, it means that you must be having same values for the DateAdded column. To get around this, you can change the sub queries to compare using a unique key such as ID, that is if your table has one. i.e. Change all occurrences of "WHERE DateAdded = mt.DateAdded" to be something like "WHERE ID = mt.ID. – zana Apr 05 '15 at 11:07
  • If there is a more than one row error, it means that you have the ame DateAdded value appearing in more than one row. In this case, you could use a more unique field (Such as ID if it exists in your table) to prevent the error. You can achieve this by changing three "WHERE DateAdded = mt.DateAdded" clauses to be something like "WHERE ID = mt.ID" – zana Apr 05 '15 at 13:41