0

I have search SO and found answers where people GROUP BY but haven't come across an answer where it displays the ability to GROUP BY (in this case date) and then get the user with the highest # of entries (in each of the date results).

I have my table set up as follows.

+----+------+-----------+-----------------+---------------------+
| id | name | reference |      email      |        date         |
+----+------+-----------+-----------------+---------------------+
|  1 | dan  |     56453 | dan@example.com | 2015-05-01 09:00:01 |
|  2 | bob  |     34564 | bob@example.com | 2015-05-01 09:21:03 |
|  3 | dan  |     08948 | dan@example.com | 2015-05-01 09:30:08 |
|  4 | bob  |     43775 | bob@example.com | 2015-05-02 09:01:43 |
|  5 | bob  |     67210 | bob@example.com | 2015-05-02 09:04:13 |
|  6 | dan  |     22195 | dan@example.com | 2015-05-02 09:09:11 |
+----+------+-----------+-----------------+---------------------+

Each day users log in and input a reference. Each day they could be logging many entries.

First, what I'm trying to do is GROUP BY the days and output the data.

So $day[0] which should equal "2015-05-01", should output those first 3 rows and who (and their count) has the highest number of entries for that day.

So output should be:

+----+------+-----------+-----------------+---------------------+
| Results of 2015-05-01                                         |
+----+------+-----------+-----------------+---------------------+
|  1 | dan  |     56453 | dan@example.com | 2015-05-01 09:00:01 |
|  2 | bob  |     34564 | bob@example.com | 2015-05-01 09:21:03 |
|  3 | dan  |     08948 | dan@example.com | 2015-05-01 09:30:08 |
+----+------+-----------+-----------------+---------------------+

+----+------+-----------+-----------------+---------------------+
| Top user/s for 2015-05-01                                     |
+-----+-----+---------------------------------------------------+
| dan |  2  |                                                   |
+-----+-----+---------------------------------------------------+

So I need to be able to output the data for each day including the user with the most entries for that particular day.

My understanding is that I'd need to do something like this but can't quite grasp it.

$query = mysqli_query('SELECT * FROM mydata GROUP BY DATE(mydata.date as $date)');
$groupcount = 0;

while ($row = @mysqli_fetch_array($query)) 
    {
        $rowcount = 0; 
        $date[$groupcount] = $date;
        $dayquery = mysqli_query('SELECT * FROM mydata WHERE date = $date)');
        while ($entry = @mysqli_fetch_array($dayquery)) 
           $data[] = array($row['$rowcount']['name'], $row['$rowcount']['reference'], $row['$rowcount']['email'], $row['$rowcount']['date']);

           foreach ($data as $d) {
             echo "Name: " . $d[0] . ", Reference: " . $d[1] . ", Email: " . $d[2] . ", Date: " . $d[3];
           }

           $rowcount++;
    }

So something like that but also having another query that will output the person and the sum of the highest entries for that day. I just don't want to add another query as I think there would be a better way of structuring this to include COUNT in another.

Could potentially be multiple people with the same number of entries for the day so I'm trying to accommodate for that.

DT.DTDG
  • 765
  • 1
  • 13
  • 31
  • `GROUP BY` in SQL combines all the rows with the same value into a single row in the results. It's usually used with aggregation functions like `SUM()`, `COUNT()` or `MAX()`. – Barmar May 01 '15 at 03:27
  • What is `GROUP BY DATE(mydata.date as $date)` supposed to be? It looks like you're expecting it to set the PHP variable `$date` to the date from that row. Where did you learn that from? – Barmar May 01 '15 at 03:30
  • @Barmar this isn't a duplicate as it is grouping by date and then requiring a count on the user/s with the most # of entries. Thought I could use `$date` to store the date in a var. – DT.DTDG May 01 '15 at 03:37
  • @Barmar could you please show an example of how I should be doing this correctly? – DT.DTDG May 01 '15 at 03:39
  • You can use http://stackoverflow.com/questions/21916186/mysql-max-count-within-groups to get the top user for each day, and http://stackoverflow.com/questions/27575562/how-can-i-list-has-same-id-data-with-while-loop-in-php/27575685#27575685 to show all the rows from the same day together. If you then put them each in subqueries and join them on the date, you can get all the results in one query. – Barmar May 01 '15 at 04:03

1 Answers1

0

This should give you what you are looking for all in one query.

SELECT
    max(m.id), -- since this column is unique you can ignore it if it's not important, or using min/max to get one of the values
    max(m.reference), -- since this column appears to be unique you can ignore it if it's not important, or using min/max to get one of the values
    m.name,
    m.email,
    cast(m.date as date) date,
    count(*) count
FROM
    mydata m
GROUP BY
    m.name,
    m.email,
    cast(m.date as date)
ORDER BY
    m.date DESC,
    m.count DESC,
    m.name ASC,
    m.email ASC;
Jonathan
  • 2,778
  • 13
  • 23
  • Thanks for that. Going by my example above are you able to edit to show how I can get all the data for all the dates (in a loop and outputting the data) and then within that loop still showing the person or persons with the most number of entries? Happilly will accept your answer, just looking for an answer that integrates with what I'm trying to do with PHP as well for output of the two things (all data for each data & person/persons with the most number of entries for that date). Thank you! – DT.DTDG May 01 '15 at 07:34
  • have you used my query to attempt to figure it out on your own yet? the above query will return data for all days ordered by the day followed by the users with the highest count, as such using the first entry for each day will tell you the top user for the day. – Jonathan May 01 '15 at 13:16
  • Thanks @Augwa I'll give this a try shortly. – DT.DTDG May 03 '15 at 08:35