GROUP BY multiple row and want to show more than 1 record

Question

I have a store table, and the sql is

SELECT * FROM `store` GROUP BY `store_name`, `country`, `branch`

The output is

store_name	country	branch
store_a	US	New_York
store_a	US	Texas
store_b	Japan	Tokyo
store_c	German	Hamburg
store_c	German	Berlin
store_c	German	Hesse
store_d	French	Paris

Now I want to show stores have more than 1 branches

This is what I expect:

store_name	country	branch
store_a	US	New_York
store_a	US	Texas
store_c	German	Hamburg
store_c	German	Berlin
store_c	German	Hesse

How to achieve this?

1. If there are more than the three columns in the table, then `SELECT * FROM store GROUP BY store_name, country, branch` is invalid. If there are just the three columns in the table, then your query can be simplified to `SELECT * FROM store`. 2. In your sample data the same store name happens to be in the same country always. But what about the same store name in two different countries? Do you conisder these two rows for the store and show the rows or one row per store/country and you don't show the rows? — Thorsten Kettner, May 19 '22 at 15:02

score 1 · Answer 1 · answered May 19 '22 at 14:45

1

That is what the HAVING clause is for.

Try

SELECT * 
FROM `store` 
GROUP BY `store_name`, `country`, `branch`
HAVING COUNT(*) > 1

answered May 19 '22 at 14:45

Josh

1,493
1
13
24

1

`SELECT * ` with `GROUP BY store_name, country, branch` very bad practice, try and run your query on https://dbfiddle.uk/?rdbms=mysql_8.0 – Ergest Basha May 19 '22 at 14:49
@ErgestBasha, can you please explain why is it a bad practice? – Asgar May 19 '22 at 15:16
@Asgar you have a detailed explanation [here](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) and on [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html) – Ergest Basha May 19 '22 at 15:21

score 1 · Accepted Answer · answered May 19 '22 at 14:54

Here's a solution using window functions (you must use MySQL 8.0 for this feature):

select store_name, country, branch from (
  select store_name, country, branch, 
    count(*) over (partition by store_name) as count
  from store
) as t
where count > 1;

No GROUP BY is needed.

score 0 · Answer 3 · answered May 19 '22 at 14:49

Join with a query that gets the number of branches per country and only returns the ones that have more than one branch.

SELECT a.*
FROM store AS a
JOIN (
    SELECT store_name, country
    FROM store
    GROUP BY store_name, country
    HAVING COUNT(*) > 1
) AS b ON a.store_name = b.store_name AND a.country = b.country

GROUP BY multiple row and want to show more than 1 record

3 Answers3