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: stackoverflow.com/questions/30699602/… — O. Jones
– O. Jones, Commented Jun 8, 2015 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
– Gordon Linoff, Commented Jun 8, 2015 at 11:40

Anwar · Accepted Answer · 2015-06-08 11:00:21Z

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 8, 2015 at 11:00

Anwar

4,3065 gold badges46 silver badges67 bronze badges

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

1 Comment

zion ben yacov Over a year ago

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 · Accepted Answer · 2015-06-08 11:02:13Z

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 8, 2015 at 11:02

zion ben yacov

7356 silver badges13 bronze badges

1 Comment

julious ceazer Over a year ago

I looking for a solution without group_concat(). Thanks

Collectives™ on Stack Overflow

Get datewise result as grouped from MySql Database

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

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

Linked

Related