mysql - How do I use another column if two rows in the first column checked are the same?

Question

I have a table that looks something like this:

stones

ID	number	snumber	wt	qt
1	a		11.5	13
2	c		4.0	8
3	a	a1	1.0	2
4	d		0.5	1
5	b		2.0	4
6	a	a2	5.0	1
7	f		3.0	6
8	a	a3	5.0	10

question from:https://stackoverflow.com/questions/65912042/how-do-i-use-another-column-if-two-rows-in-the-first-column-checked-are-the-same

Answer 1

The error is at END AS number OR snumber. After you remove OR snumber, there won't be any error but there won't be any result too; against the example data you've provided. Try changing WHERE x.totalrows = 1 AND wt >= 2.5 to WHERE wt >= 2.5 only because you're already doing a CASE expression:

CASE 
        WHEN x.totalrows > 1
            THEN stones.snumber
            ELSE stones.number
        END AS number

So adding WHERE x.totalrows = 1 won't yield anything from the CASE.

Demo fiddle

Update:

Well, after long discussion in comment, I think I got it:

SELECT inv.ID, 
       CASE WHEN inv.snumber <> "" 
            THEN inv.snumber ELSE inv.number 
            END AS numberORSnumber, 
       inv.wt,
       inv.qt,
       COUNT(*) OVER() AS totalrows,
       CASE WHEN inv.number=v.number 
            AND inv.snumber="" THEN 0 ELSE 1 END AS Checking
FROM stones inv 
CROSS JOIN 
(SELECT number FROM stones WHERE snumber <> "" group by number) v
WHERE wt >= 2.5
HAVING Checking <> 0
ORDER BY ID ASC;

I end up adding a CROSS JOIN with a subquery that return number value that have snumber as per OP requirement. Then with that, I use CASE to do the checking and finally use HAVING to filter them out from the checking.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=40cb88da028a42dc147dc4224154ab05