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

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?

CodePudding user response：

That is what the HAVING clause is for.

Try

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

CodePudding user response：

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

CodePudding user response：

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.