Group MySQL Results by Category and Display Them into Groups Under Each Category

SQL query for multiple groups combining each group as an AND statement and within each group as an OR statement

So, you seem to have a table category_posts with multiple rows per entry_id. You want the categories on the various rows of an entry_id to meet your conditions.

This requires a having clause:

select cp.entry_id
from category_posts cp left outer join
     group1 g1
     on cp.cat_id = g1.cat_id left outer join
     group2 g2
     on cp.cat_id = g2.cat_id left outer join
     group3 g3
     on cp.cat_id = g3.cat_id
group by cp.entry_id
having max(case when g1.cat_id is not null then 1 else 0 end) = 1 and
       max(case when g2.cat_id is not null then 1 else 0 end) = 1 and
       max(case when g3.cat_id is not null then 1 else 0 end)

That is, there is at least one representative category from each group.

Group mysqli/PDO results by category and display them into groups

The simplest way to get to what you're looking for is probably a simple GROUP_CONCAT which will give you a comma separated list of images per category;

SELECT cat_name, GROUP_CONCAT(image ORDER BY image.id) images
FROM category 
JOIN image
  ON category.id = image.catid
GROUP BY category.id

cat_name    images
-----------------------------
Category 1  A.jpg,B.jpg,C.jpg
Category 2  D.jpg,E.jpg

An SQLfiddle to test with.

Get top n records for each group of grouped results

Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries for each group:

(
  select *
  from mytable 
  where `group` = 1
  order by age desc
  LIMIT 2
)
UNION ALL
(
  select *
  from mytable 
  where `group` = 2
  order by age desc
  LIMIT 2
)

There are a variety of ways to do this, see this article to determine the best route for your situation:

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Edit:

This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 2:

select person, `group`, age
from 
(
   select person, `group`, age,
      (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
  from test t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `Group`, Age desc, person
) as x 
where x.row_number <= 2;

See Demo

Group and include all categories in SQL

You need a Cross Join of the categories and the groups first and then a Left Join:

select c.category, g.group, coalesce(amount, 0)
from
 ( -- all categories
   select distinct Category from tab
 ) as c
cross join -- create all possible combinations
 ( -- all groups
   select distinct group from tab
 ) as g
left join tab as a -- now join back the amount
  on c.category = a.category
 and g.group = a.Group

Displaying and grouping two joined tables in MySQL

There is basically two ways of doing what you want, both of them have been explained in the other response and in the comments. I will try to expand a little about them in this answer.

One query

You retrieve all the data in one query and then use PHP to do some transformation in order to show them in the appropriate way.

There's two approaches to do that. You can modify the data first and then loop on them to display the menu or you can do everything in just one loop.

Two steps variant

First, we create an array containing the data in a more "usable" way for us, and then we display the content of this new array :

$sql = "SELECT categories.id as catid, categories.name as catname, products.id as prodId, products.name as prodName 
        FROM `categories` 
        INNER JOIN `products` ON categories.id = products.category 
        ORDER BY categories.id DESC";

$result = execute_select($sql);
$categories = array();
foreach($query as $row) {
    if( ! isset($categories[$row['catid']])) {
        $categories[$row['catid']] = array(
            'id' => $row['catid'],
            'name' => $row['catname'],
            'products' => array(),
        );
    }
    $categories[$row['catid']]['products'][] = array(
            'id' => $row['prodId'],
            'name' => $row['prodName'],
    );
}
foreach($categories as $cat) {
    echo '<li><a href="#">' . $cat['name'] . '</a><ul>';
    foreach($cat['products'] as $prod) {
        echo '<li><a href="&id=' . $prod['id'] . '">' . $prod['name'] . '</a></li>';
    }
    echo '</ul></li>';
}

One step variant

We store the current category, and when the category changes, we close the current list and open a new one :

$sql = "SELECT categories.id as catid, categories.name as catname, products.id as prodId, products.name as prodName 
        FROM `categories` 
        INNER JOIN `products` ON categories.id = products.category 
        ORDER BY categories.id DESC";

$result = execute_select($sql);
$actualcategory = null;
foreach($query as $row) {
    if($actualcategory != $row['catid']) {
        echo '<li><a href="#">' . $row['catname'] . '</a><ul>';
    }
    echo '<li><a href="&id=' . $row['prodId'] . '">' . $row['prodName'] . '</a></li>';
    if($actualcategory != $row['catid']) {
        echo '</ul></li>';
        $actualcategory = $row['catid'];
    }
}

n+1 query

In this solution, we retrieve the list of categories, and then, for each one, retrieves the list of products :

$sql = "SELECT categories.id, categories.name
        FROM `categories` 
        ORDER BY categories.id DESC";
$categories = execute_select($sql);
foreach($categories as $cat) {
    echo '<li><a href="#">' . $cat['name'] . '</a><ul>';
    $sql2 = "SELECT products.id, products.name
            FROM `products` 
            WHERE `products`.category = ".$cat['id'];
    $products = execute_select($sql2);
    foreach($products as $prod) {
        echo '<li><a href="&id=' . $prod['id'] . '">' . $prod['name'] . '</a></li>';
    }
    echo '</ul></li>';
}

Dry coding warning

I dry coded the preceding piece of PHP code, I'm not even sure I didn't made some kind of silly mistakes. It is possible you will have to adapt them to your needs. If something is wrong, please point it out in the comments, I will fix it ASAP :)

Conclusion

The first two possibilities executes only one query and then parse the results to display meaningful information. The code is, in my opinion, fairly hard to understand and error prone.

The last possibility is much more clearer and, I think, easier to modify and extend.

From a performance point of view, we have only one query in the first two versions, but we retrieves much more data and a join in necessary. I think there's no easy answer about which solution is best, it will greatly depends on the number of categories and products for each of them. I think the best to do is to test each of the solution on various data set to determine the quicker one.

If I would have to develop this kind of menu, I will personally use the n+1 query approach. Even if the performance are slightly off (which I'm not sure), the solution is so much clearer that it compensates the weaknesses.

Disclaimer

Kudos to every other poster on this question, I didn't provide a "new" answer, I just put the already provided one in PHP code. Hope this will help !

Get records with max value for each group of grouped SQL results

There's a super-simple way to do this in mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.

You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)

Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.

Version 5.7 update:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

PHP & MYSQL: using group by for categories

I'd recommend just a simple query to fetch all the rows, sorted by category id. Output the category only if its value changes from the previous row.

<?php

$stmt = $pdo-> query("SELECT * FROM `myTable` ORDER BY categoryID");

$current_cat = null;
while ($row = $stmt->fetch()) {
  if ($row["categoryID"] != $current_cat) {
    $current_cat = $row["categoryID"];
    echo "Category #{$current_cat}\n";
  }
  echo $row["productName"] . "\n";
}

?>

Get n grouped categories and sum others into one

The specific difficulty here: Queries with one or more aggregate functions in the SELECT list and no GROUP BY clause produce exactly one row, even if no row is found in the underlying table.

There is nothing you can do in the WHERE clause to suppress that row. You have to exclude such a row after the fact, i.e. in the HAVING clause, or in an outer query.

Per documentation:

If a query contains aggregate function calls, but no GROUP BY clause,
grouping still occurs: the result is a single group row (or perhaps no
rows at all, if the single row is then eliminated by HAVING). The same
is true if it contains a HAVING clause, even without any aggregate
function calls or GROUP BY clause.

It should be noted that adding a GROUP BY clause with only a constant expression (which is otherwise completely pointless!) works, too. See example below. But I'd rather not use that trick, even if it's short, cheap and simple, because it's hardly obvious what it does.

The following query only needs a single table scan and returns the top 7 categories ordered by count. If (and only if) there are more categories, the rest is summarized into 'Others':

WITH cte AS (
   SELECT categoryid, count(*) AS data
        , row_number() OVER (ORDER BY count(*) DESC, categoryid) AS rn
   FROM   contents
   GROUP  BY 1
   )
(  -- parentheses required again
SELECT categoryid, COALESCE(ca.name, 'Unknown') AS label, data
FROM   cte
LEFT   JOIN category ca ON ca.id = cte.categoryid
WHERE  rn <= 7
ORDER  BY rn
)
UNION ALL
SELECT NULL, 'Others', sum(data)
FROM   cte
WHERE  rn > 7         -- only take the rest
HAVING count(*) > 0;  -- only if there actually is a rest
-- or: HAVING  sum(data) > 0

• You need to break ties if multiple categories can have the same count across the 7th / 8th rank. In my example, categories with the smaller categoryid win such a race.

• Parentheses are required to include a LIMIT or ORDER BY clause to an individual leg of a UNION query.

• You only need to join to table category for the top 7 categories. And it's generally cheaper to aggregate first and join later in this scenario. So don't join in the the base query in the CTE (common table expression) named cte, only join in the first SELECT of the UNION query, that's cheaper.

• Not sure why you need the COALESCE. If you have a foreign key in place from contents.categoryid to category.id and both contents.categoryid and category.name are defined NOT NULL (like they probably should be), then you don't need it.

The odd GROUP BY true

This would work, too:

...

UNION ALL
SELECT NULL , 'Others', sum(data)
FROM   cte
WHERE  rn > 7
GROUP BY true; 

And I even get slightly faster query plans. But it's a rather odd hack ...

SQL Fiddle demonstrating all.

Related answer with more explanation for the UNION ALL / LIMIT technique:

• Sum results of a few queries and then find top 5 in SQL

---

Related Topics