MYSQL - Using GROUP with JOIN

Question

So this statement started off without the join, it would simply lookup the product tables and group results by item_code.

SELECT a.item_code,
       a.price,
       b.v_no
FROM product a
JOIN variation b ON a.item_code = b.item_code
WHERE a.price>=2
GROUP BY a.item_code

Now that I have added the join I want to group by v_no but ONLY if data v_no exists. This is because not every item from the product table has a corresponding entry in the variation table. So group by item_code for 'normal' products, but group by v_no THEN item_code if a v_no exists.

The below does not work:

SELECT a.item_code,
       a.price,
       b.v_no
FROM product a
JOIN variation b ON a.item_code = b.item_code
WHERE a.price>=2
GROUP BY b.v_no,
         a.item_code

The item_code grouping remains but the v_no grouping is ignored.

Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Ref: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 26, 2018 at 10:06

Madhur Bhaiya · Accepted Answer · 2018-10-26 10:10:25Z

1

In order to consider all the product(s) (with/without variations), you will need to change to LEFT JOIN.
For conditional Group By, you can use Coalesce() function; it will consider the first non-null value for Grouping upon.

Try the following:

SELECT a.item_code,
       a.price,
       b.v_no
FROM   product a
       LEFT JOIN variation b
         ON a.item_code = b.item_code
WHERE  a.price >= 2
GROUP  BY COALESCE(b.v_no, a.item_code)

answered Oct 26, 2018 at 10:10

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Madhur Bhaiya Over a year ago

@Guesser Happy to help. Thanks :)

Collectives™ on Stack Overflow

MYSQL - Using GROUP with JOIN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related