Count and select all dates for a specific field in MySQL

Question

i have a data format like this:

+----+--------+---------------------+
| ID | utente | data                |
+----+--------+---------------------+
| 1  | Man1   | 2014-02-10 12:12:00 |
+----+--------+---------------------+
| 2  | Women1 | 2015-02-10 12:12:00 |
+----+--------+---------------------+
| 3  | Man2   | 2016-02-10 12:12:00 |
+----+--------+---------------------+
| 4  | Women1 | 2014-03-10 12:12:00 |
+----+--------+---------------------+
| 5  | Man1   | 2014-04-10 12:12:00 |
+----+--------+---------------------+
| 6  | Women1 | 2014-02-10 12:12:00 |
+----+--------+---------------------+

I want to make a report that organise the ouptout in way like this:

+---------+--------+-------+---------------------+---------------------+---------------------+
| IDs     | utente | count | data1               | data2               | data3               |
+---------+--------+-------+---------------------+---------------------+---------------------+
| 1, 5    | Man1   | 2     | 2014-02-10 12:12:00 | 2014-04-10 12:12:00 |                     |
+---------+--------+-------+---------------------+---------------------+---------------------+
| 2, 4, 6 | Women1 | 3     | 2015-02-10 12:12:00 | 2014-03-10 12:12:00 | 2014-05-10 12:12:00 |
+---------+--------+-------+---------------------+---------------------+---------------------+

All the row thath include the same user (utente) more than one time will be included in one row with all the dates and the count of records. Thanks

What if Women1 shows up one more time, with ID = 7, do you want another column data4 in that case, dynamically? (BTW, "man" is singular, "women" is plural.) — jarlh
– jarlh, Commented Mar 17, 2015 at 15:46
@jarlh .Yes, but i've made a count and data shows at max 6 time in a count(*) query. Thanks — markserver
– markserver, Commented Mar 17, 2015 at 15:57

fthiella · Accepted Answer · 2015-03-17 16:12:43Z

3

While it's certainly possible to write a query that returns the data in the format you want, I would suggest you to use a GROUP BY query and two GROUP_CONCAT aggregate functions:

SELECT
  GROUP_CONCAT(ID) as IDs,
  utente,
  COUNT(*) as cnt,
  GROUP_CONCAT(data ORDER BY data) AS Dates
FROM
  tablename
GROUP BY
  utente

then at the application level you can split your Dates field to multiple columns.

answered Mar 17, 2015 at 16:12

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

markserver Over a year ago

Thanks, this is the best solution, with this and the Workbench i can export directly the result to Excel.

Peter Bowers · Accepted Answer · 2015-03-17 16:14:54Z

1

Looks like a fairly standard "Breaking" report, complicated only by the fact that your dates extend horizontally instead of down...

SELECT * FROM t ORDER BY utente, data
$lastutente = $lastdata = '';
echo "<table>\n";
while ($row = fetch()) {
    if ($lastutente != $row['utente']) {
        if ($lastutente != '') {
            /****
             * THIS SECTION REF'D BELOW
             ***/
            echo "<td>$cnt</td>\n";
            foreach ($datelst[] as $d) 
                echo "<td>$row[data]</td>\n";
            for ($i = count($datelst); $i < $NumberOfDateCells; $i++)
                echo "<td>&nbsp;</td>\n";
            echo "</tr>\n";
            /****
             * END OF SECTION REF'D BELOW
             ***/
        }
        echo "<tr><td>$row[utente]</td>\n"; // start a new row - you probably want to print other stuff too
        $datelst = array();
        $cnt = 0;
    }
    if ($lastdata != $row['data']) {
        datelst[] = $row['data'];
    }
    $cnt += $row['cnt']; // or $cnt++ if it's one per row
}
print the end of the last row - see SECTION REF'D ABOVE
echo "</table>\n";

You could add a GROUP BY utente, data to your query above to put a little more load on mysql and a little less on your code - then you should have SUM(cnt) as cnt or COUNT(*) as cnt.

edited Mar 17, 2015 at 16:14

answered Mar 17, 2015 at 15:48

Peter Bowers

3,1031 gold badge13 silver badges20 bronze badges

1 Comment

markserver Over a year ago

Hi Peter, thanks for reply, can you write up a more concrete example on how to do this in php? I will use it only for getting data and sending it in Excel to the Chief. Thanks a lot

Collectives™ on Stack Overflow

Count and select all dates for a specific field in MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related