Get records for each group of grouped results

Question

I am hoping you can help me out with my querying and displaying results in while loop.

I stumbled upon several similar questions such as this one and although it kind of answers my question it also kind of not.

I have following query:

(SELECT  dt_id, DATE_FORMAT(dt_date, '%m-%d-%Y') as dt_date,
         dt_data, dt_bodypart, dt_wktype, dt_wkurl, dt_year,
         COUNT(*) as Total
 FROM data
 WHERE dt_year = '2015'
   and dt_username_member = 'admin'
 GROUP BY dt_date
 ORDER BY dt_date DESC
 LIMIT 0,100)
ORDER BY dt_date ASC

Which displays rows by date and group them by date as well since my array that handles results cannot display more than 1 results in calendar if same date appears twice. I also count how many records are for each date.

I actually can make it to work as I like by running query inside of a while loop (which is bad practice) but I would like to avoid that and do it in more efficient and better way

Can't work with results like this as my array will only display one result on calendar page (most recent one added to db) :

array {
'08-26-2015' : 'something',
'08-26-2015' : 'something else',
'08-27-2015' : 'else',
}

Currently I have this but also I am running query in while loop if count of records for that date is > 1 and I would like to achieve this very same result but without running query for records with count > 1 inside of a while loop

array {
'08-26-2015' : 'something, something else',
'08-27-2015' : 'else',
}

Is that DISTINCT really needed? (Makes me confused...) – jarlh Sep 01 '15 at 13:47 — jarlh, Sep 01 '15 at 13:47
I guess not. I corrected the query. Thanks! – vpetkovic Sep 01 '15 at 13:59 — vpetkovic, Sep 01 '15 at 13:59

score 0 · Accepted Answer · answered Sep 04 '15 at 11:17

After some digging I found the solution. I came up with query that will retrieve all the results without me needing to run queries inside the while loop in order to obtain all information for each day. Here's the query:

(SELECT DATE_FORMAT(dt_date, '%m-%d-%Y') dt_date, 
count(dt_date) as date_count,
GROUP_CONCAT(dt_data ORDER by dt_date SEPARATOR '>>') dt_data, 
GROUP_CONCAT(dt_bodypart ORDER by dt_date SEPARATOR '>>') dt_bodypart,
GROUP_CONCAT(dt_wktype ORDER by dt_date SEPARATOR '>>') dt_wktype,
GROUP_CONCAT(dt_wkurl ORDER by dt_date SEPARATOR '>>') dt_wkurl
FROM data 
WHERE dt_username_member = 'admin' and dt_year = '2015'
GROUP BY dt_date ORDER BY dt_date ASC) ORDER BY dt_date ASC

This query will group all information into a row that matches particular day just like this:

dt_date     |  dt_data                   |  
------------------------------------------
2015-09-03  |  something>>something else |

So all you need to do after is to explode dt_data and you'll.

Get records for each group of grouped results

1 Answers1