Mysql Group by One Column, but Select All Data

Question

I've got a database which contains lots of data. I would like to select all data, but Group by one column.

So for example:

column a | column b
example  | apple
example  | pear
example  | orange
example  | strawberry

In the example column A is a column which has duplicate values and needs to be grouped. Column B however has different values, and I would like to select all these values.

In the end, I would like to get a query that results in:

example
- apple
- pear
- orange
- strawberry

Where the values of column B are in a jQuery accordion.

I've tried and searched for a couple of hours but still haven't found a way to do this.

EDIT: I've manage to solve the problem using the answer Gordon provided. Using PHP I exploded the query results and with a while loop I collected all the data from the arrays.

It's working great now, thanks guys!

Accepted Answer

I think you can get what you want using group_concat():

select a, group_concat(b)
from t
group by a;

This will create a list of "b"s for each a. In your example:

example    apple,pear,orange,strawberry

You can change the separator using the SEPARATOR keyword.

EDIT:

You can use group_concat() multiple times:

select a, group_concat(b) as bs, group_concat(c) as cs
from t
group by a;

Or, combine it with concat():

select a, group_concat(concat(b, ':', 'c')) as bcs
from t
group by a;

Answer 2

All SQL systems deal in tables: rectangles of data with rows and columns. Your question asks for a result set which isn't really a rectangle of data, in the sense that it contains "header" rows and "detail" rows.

 Example:    (header row)
   - apple   (detail row)

It's common practice to create such header / detail breakout in your client (php) software.

Pro tip: Remember that if you don't specify ORDER BY, MySQL (and all SQLs) are permitted to return the information in your result in any convenient order. Enlarging on Gordon's fine answer, then, you might want:

 SELECT a, 
        GROUP_CONCAT(CONCAT(b, ':', 'c') ORDER BY b,c) AS bcs
   FROM t
  GROUP BY A
  ORDER BY A

I learned this the hard way when I helped write a SQL app that was really successful. All the ordering worked great until we switched over to higher - capacity clustered access methods. Then lots of "default" ordering broke and our customers saw strange stuff until we fixed it.

Answer 3

The example live of accordion: Just implement in code below. ;)

The code to mount data:

<html>

    <head>Your page</head>

    <body>

        <div class="accordion">
            <?php echo getData() ?>
        </div>

    </body>

</html>

<?php

function getData(){

    //This get the unique values of column a
    $sql = "SELECT DISTINCT column_a FROM table";
    $result = mysql_query($sql) or die(mysql_error());

    while ($row = mysql_fetch_object($result)) {

        $html = "<h3>{$row->column_a}</h3>";

        //This get the values with column a some value
        $sql2 = "SELECT column_b FROM table WHERE column_a LIKE '%{$row->column_a}%'";
        $result2 = mysql_query($sql2) or die(mysql_error());

        $html .= '<div class="pane"><ul>';
        while ($row2 = mysql_fetch_object($result2)) {
            $html .= '<li>{$row2->column_b}</li>';
        }
        $html .= '</ul></div>';

    }

    return $html;

}

?>

Mysql Group by One Column, but Select All Data

3 Answers

Must Read

Your Answer

Chloe Bennett

Mysql Group by One Column, but Select All Data

3 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Chloe Bennett