Summarize MySQL table entries by Day

Question

I have got a MySQL Table with 4 columns: completedtasks, duration, date and educationDepartment. My first aim was to show my entries by the name of the weekday for the specific day I have. I accomplished that with following query:

SELECT DAYNAME(date) AS DAY,
       completedtasks,
       duration,
       educationDepartment
FROM programm_completedtask;

Now I want to summarize the information I have, by the day of the week. Let's say I got a table with following entries for instance:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
| Tuesday   | Some other stuff to do|        8 | Another Department  |
| Wednesday | Some other stuff to do|        6 | Test Department     |
| Thursday  | Being lazy            |        8 | Another Test        |
| Friday    | A completed task      |        8 | Another Test        |
| Monday    | Some entry            |        2 | A Test              |
| Wednesday | Test entry            |        2 | Another Test        |
+-----------+-----------------------+----------+---------------------+

I tried several queries, but those didn't give me the results I wanted to have. When I used GROUP BY it just gave me on of the entries I had in the table like this:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
+-----------+-----------------------+----------+---------------------+

This query:

SELECT DAYNAME(date) AS Day,
       completedtasks,
       duration,
       educationDepartment
FROM programm_completedtask
WHERE DAYNAME(date)
LIKE "Monday";

gave me following result.

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do      |        6 | A Department        |
| Monday    | Some entry            |        2 | A Test              |
+-----------+-----------------------+----------+---------------------+

My aim is to get a result like this:

+-----------+-----------------------+----------+---------------------+
| Day       | completedtasks        | duration | educationDepartment |
+-----------+-----------------------+----------+---------------------+
| Monday    | Some stuff to do,     |       6,2| A Department, A Test|
|           | Some entry            |          |                     |
+-----------+-----------------------+----------+---------------------+

So, I want to summarize the entries for one specific weekday and I want it in one row. I hope that this is even possible in some way. I appreciate the help very much !

I would love to accept both answers because they are obviously both correct but I can just give one this honour :/ — BoJack Horseman, Apr 16 '14 at 08:18

score 2 · Answer 1 · answered Apr 16 '14 at 08:09

2

SELECT DAYNAME(date) as `Day`,
        GROUP_CONCAT(completedtasks ) as completedTasks,
        GROUP_CONCAT(duration ) as duration ,
        GROUP_CONCAT(educationDepartment ) as educationDepartment  ,
FROM tableName
GROUP BY DAYNAME(date)

No need to specify SEPERATOR as default is comma character

answered Apr 16 '14 at 08:09

Mudassir Hasan

28,083
20
99
133

Thanks, I didn't know that was the default separator. – mjsqu Apr 16 '14 at 08:41

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

This should work:

SELECT DAYNAME(date) AS Day, 
group_concat(completedtasks separator ',') as completedtasks, 
group_concat(duration separator ',') as duration, 
group_concat(educationDepartment separator ',') as educationdepartment 
FROM programm_completedtask
group by dayname(date);

See this answer: How to use GROUP BY to concatenate strings in MySQL?

edited May 23 '17 at 10:32

Community

1
1

answered Apr 16 '14 at 08:12

mjsqu

5,151
1
17
21

Summarize MySQL table entries by Day

2 Answers2