How to group rows in MySQL with condition

Question

I have this table:

| id | part number | serial_number | qty |
==========================================
| 1  | A           | 12345         | 1   |
| 2  | A           | 54321         | 1   |
| 3  | A           | 67890         | 1   |
| 4  | B           |               | 10  |
| 5  | B           |               | 5   |
| 6  | C           |               | 6   |
| 7  | C           |               | 3   |

I want to group the rows like this:

| id | part number | serial_number | qty |
==========================================
| 1  | A           | 12345         | 1   |
| 2  | A           | 54321         | 1   |
| 3  | A           | 67890         | 1   |
| 4  | B           |               | 10  |
| 6  | C           |               | 6   |

How to do this? Is this possible or not?

I want group the row by part number that doesn't have serial number. So if I have 4 rows with same part number that doesn't have serial number, it's only display 1 row.

I have voted to reopen as the joining logic needed here did some seem to be covered by the duplicate link. — Tim Biegeleisen, Feb 27 '19 at 08:18
Welcome to Stack Overflow. In your result table, you do not seem to getting a total by part_number, just the "first" value for qty. Is that your intent? Ir is it the max value for the combination of part number and serial number? Please edit your question to clarify these points. — Sloan Thrasher, Feb 27 '19 at 08:39
@SloanThrasher Re-read the question/data. `NULL` is a completely legitimate value as part of a group. Hence, the last two records have `NULL` for the serial number. — Tim Biegeleisen, Feb 27 '19 at 08:50
@TimBiegeleisen, I wasn't questioning null being legitimate. Whether the serial number is null or blank doesn't matter to me. The point of my comment had to do with which qty to include in the result: The highest value or the first value in a group. — Sloan Thrasher, Feb 27 '19 at 21:54

Tim Biegeleisen · Answer 1 · 2019-02-27T09:02:33.590

2

This looks like you just want to aggregate by the combination of the part number and serial_number, taking the max quantity:

SELECT
    MIN(t1.id) AS id, t1.part_number, t1.serial_number, t1.qty
FROM yourTable t1
INNER JOIN
(
    SELECT part_number, serial_number, MAX(qty) AS qty
    FROM yourTable
    GROUP BY part_number, serial_number
) t2
    ON t1.part_number = t2.part_number AND
       (t1.serial_number = t2.serial_number OR
        t1.serial_number IS NULL AND t2.serial_number IS NULL) AND
       t1.qty = t2.qty
GROUP BY
    t1.part_number,
    t1.serial_number,
    t1.qty
ORDER BY
    MIN(t1.id);

Demo

Note that null is a legitimate value to form a member of a group in a GROUP BY operation.

edited Feb 27 '19 at 09:02

answered Feb 27 '19 at 08:09

Tim Biegeleisen

502,043
27
286
360

i try this, but when i have the part number with same qty is displayed 2 rows. i want the group by is only for part number that doesn"t have serial number. so if i have part number with same qty, it"s only have 1 row. – Nur Oktaviani Feb 27 '19 at 08:55
@NurOktaviani One fix for this would be to aggregate the ou tr query by part number, serial number, and quantity, and then arbitrarily retain the lowest `id` value. Another option, if you are using MySQL 8+, woudl be to use `ROW_NUMBER`. – Tim Biegeleisen Feb 27 '19 at 09:03

LukStorms · Answer 2 · 2019-02-27T09:59:33.930

2

This looks more like a job for NOT EXISTS.

For those that have a NULL serial_number you want only to keep those with the lowest id per part_number.

So you just need to filter those you don't need.

SELECT *
FROM YourTable t
WHERE NOT EXISTS
(
   SELECT 1 
   FROM YourTable d
   WHERE d.`part number` = t.`part number`
     AND d.serial_number IS NULL
     AND t.serial_number IS NULL
     AND d.id < t.id
)
ORDER BY id;

Result:

id | part number | serial_number | qty
-- | ----------- | ------------- | ---
 1 | A           |         12345 |   1
 2 | A           |         54321 |   1
 3 | A           |         67890 |   1
 4 | B           |         null  |  10
 6 | C           |         null  |   6

Test on db<>fiddle here

Btw, in MySql 5.x, when the ONLY_FULL_GROUP_BY setting isn't active.
Then this would give the same result.
But then it won't only be for those with NULL serial_number.

SELECT *
FROM YourTable 
GROUP BY `part number`, serial_number
ORDER BY id

Test here

edited Feb 27 '19 at 09:59

answered Feb 27 '19 at 09:13

LukStorms

28,916
5
31
45

I read the OP as wanting to just do an aggregation by part and serial number, but maybe I am wrong. – Tim Biegeleisen Feb 27 '19 at 09:17
@TimBiegeleisen Well, based on the title one would assume that. But based on those expected results, getting the first for those NULL's, one could interpret it differently. In MySql 8 I would probably use a ROW_NUMBER for that, but alas the majority still uses 5.x. – LukStorms Feb 27 '19 at 09:31
Agree about using `ROW_NUMBER` +1. – Tim Biegeleisen Feb 27 '19 at 10:08
It depends what you expect from the result. This would return those with the minimum id. That's how I understood it. But if you were expecting those with the maximum qty, then it would be a different query. For your sample records both would return the same result, since those minimum id have the MAX(qty) – LukStorms Feb 28 '19 at 09:00

How to group rows in MySQL with condition

2 Answers2

Demo