CODEWITHSUNDEEP
sqlhiveql
chris
chris

Reputation: 71

SQL- How to combine rows?

I have about a dozen or so devices I need to search on, and some of them have a variant. For example, if I do a GROUP BY device, it looks something like this:

Device Amount
X1     10  
X2     2
X2-A   6
X3     9
X3-A   3
X4     8
X5     11
etc...

Personally, I don't care about the variants, so after I search I have to go into Excel and manually add the rows for X2 and X2A. Is there a way to combine them in my search query?

Upvotes: 2

Views: 57

Answers (2)

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

if the variants begin with '-', We will remove the text to the right of '-'

PostgreSQL

SELECT SUBSTRING(Device FROM  1, (LENGTH(Device)-POSITION('-' IN Device))) AS DeviceSub, sum(Amount)
FROM t
GROUP BY DeviceSub;

Oracle

SELECT SUBSTR(Device, INSTR(Device, '-', 1), (LENGTH(Device)-INSTR(Device, '-', 1))) AS DeviceSub, sum(Amount)
    FROM t
    GROUP BY DeviceSub;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Well, if these are always two characters, you can do:

select substr(Device, 1, 2) as Device, sum(Amount)
from t
group by substr(Device, 1, 2);

If the expression can be of variable length, then you can have a more complicated string expression.

Upvotes: 2

Related Questions