GROUP BY one column but not the other column within same table - SQL

Question

I have a table called "department_categories" that has 3 columns "id", "category" and "department":

id | category    | department

1  | chainsaw    | garden and home
2  | jet-ski     | lifestyle
3  | generator   | garden and home
4  | tractor     | agriculture
5  | rtv         | lifestyle

Now what I want to achieve is to display all categories within each department. The following query

$sql = "SELECT category, department FROM department_categories GROUP BY department

results in the following:

garden and home | lifestyle | agriculture
                |           |
chainsaw        | jet-ski   | tractor

Which makes sense as GROUP BY groups all rows based the specified column. However the result I'm looking for is:

garden and home | lifestyle | agriculture
                |           |
chainsaw        | jet-ski   | tractor
generator       | rtv

I came across the following stackoverflow question. Which seems like a similar issue I'm having but I can't wrap my head around it.

Here is the full script I'm working with for more clarity:

<?php
$host       = "localhost";
$username   = "root";
$password   = "";
$dbname     = "auro-rudamans";
$dsn        = "mysql:host=$host;dbname=$dbname";
$options    = array(
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
              );

$connection = new PDO($dsn, $username, $password, $options)

try {

    $sql = "SELECT category, department FROM department_categories GROUP BY department";

        $statement = $connection->prepare($sql);
        $statement->execute();
        $result = $statement->fetchAll();
    }

    catch(PDOException $error) {
      echo $sql . "<br>" . $error->getMessage();
    };      
?>

<?php foreach ($result as $row) : ?>
<ul>
    <h1><?php echo $row['department']; ?></h1>
    <li><?php echo $row['category']; ?></li>
</ul>
<?php endforeach; ?>

Any assistance would greatly be appreciated. Thanks

Your first query can't be producing the result you show. You only select 2 columns, how are you getting 3 columns in the output? — Barmar, Feb 18 '21 at 09:45
And a query can't return different numbers of columns in different rows. Use `GROUP_CONCAT()` to collect all the categories into a delimited string. — Barmar, Feb 18 '21 at 09:47
@Barmar thanks for your reply. I understand there is only 2 columns. Looking at the department column there are 3 different values (garden, lifestyle, agri) and in the other column categories. In other words I just want to list every department once and underneath each department list the category under the relevant department. With the GROUP BY I get the result I'm looking for but it only shows 1 category per department as opposed to showing all categories for a department. So the output is still 2 columns. Hope I'm making sense — Mocke, Feb 18 '21 at 11:14
You wrote "results in the following:". There's no way that query can result in what you showed. — Barmar, Feb 18 '21 at 14:54
`SELECT department, GROUP_CONCAT(category) AS categories FROM department_categories GROUP BY department` — Barmar, Feb 18 '21 at 14:59

score 0 · Answer 1 · edited Feb 18 '21 at 14:56

it can be done by different approaches but if you want to this by your php code then you should execute a query in which you will get just departments

select departments from department_categories group by departments

and pass this query in while loop and get just only department in variable

$department = row['departments'];

and then call another query to call in the while loop to fetch categories

select * from department_categories  where department = $department

$query = "select * from department_categories group by departments;
$result = mysqli_query($query);

while($row = mysqli_fetch_array($result)){
    $department = $row['departments'];
    $category_query = "select * from departments where department = $departments";
    
    $result_categories = mysqli_query($category_query);
    
    while($row_category = mysqli_fetch_array($result_categories)){
        echo $row_category['category'];
    }

}

You're missing quotes around `$departments` in `$category_query`. It would be better to use a prepared statement. — Barmar, Feb 18 '21 at 14:56
`$query` should be `SELECT DISTINCT department FROM department_categories` — Barmar, Feb 18 '21 at 14:58

GROUP BY one column but not the other column within same table - SQL

1 Answers1