Get datewise result as grouped from MySql Database

Question

I would like to select data from a Mysql table as grouped data. I have a table like below in MySql Database.

+-----------------------+
|   date         name   |
+-----------------------+
| 12/02/2015   computer |
| 12/02/2015   mouse    |
| 12/02/2015   Book     |
| 13/02/2015   Paper    |
| 13/02/2015   Pen      |
| 13/02/2015   Pencil   |
+-----------------------+

I would like to display data like below.

+---------------+
| date          |
+---------------+
| 12/02/2015    |
+---------------+
| name          |
+---------------+
| computer      |
| mouse         |
| Book          |
+---------------+
| date          |
+---------------+
| 13/02/2015    |
+---------------+
| name          |
+---------------+
| Paper         |
| Pen           |
| Pencil        |
+---------------+

I need the Query. Thanks

Update

I would not like to use group_concat().I think there are other ways to do that. There are some limitation of group_concat() like it can concate certain amount of text and I am facing that type of problem.

I think here are some solutions MySQL Query with Group By

Thanks

A similar question came up recently. See this: http://stackoverflow.com/questions/30699602/how-to-formulate-query-to-show-all-courses-taken-by-a-person — O. Jones, Jun 08 '15 at 10:58
You should handle the display in the application (php) code. Your format is not really a "database" format. For instance, you are missing different types in one column. — Gordon Linoff, Jun 08 '15 at 11:40

score 0 · Answer 1 · answered Jun 08 '15 at 11:00

0

Use GROUP BY conditional SQL function to perform this task. It will group the results of your query by date, so you will have an order of names sorted by date :

SELECT T.date, T.name
FROM   TABLE T
GROUPE BY T.date, T.name -- T.name is not necessary

Which will be display as below :

DATE       NAME
-------------------
12/02/2015 Computer
12/02/2015 Mouse
12/02/2015 Book
13/02/2015 Paper
13/02/2015 Pen
13/02/2015 Pencil

answered Jun 08 '15 at 11:00

Anwar

4,162
4
41
62

1

this query will not return all the values, the mouse,book,pen and pencil will not be returned. you have to concat the names into one value – zion ben yacov Jun 08 '15 at 11:04

score 0 · Answer 2 · answered Jun 08 '15 at 11:02

0

select date, group_concat(name) as names
from table
group by date

the result will be

12/02/2015 compute,mouse,book

13/02/2015 paper,pen,pencil

then you can loop the result and explode the names value into array

answered Jun 08 '15 at 11:02

zion ben yacov

715
6
13

I looking for a solution without `group_concat()`. Thanks – julious ceazer Jun 08 '15 at 11:06

Get datewise result as grouped from MySql Database

2 Answers2