Summing UP rows in Oracle SQL query

Question

I have a Oracle (12c) table like below:

ID	Amt	Identifier
ABC	50	20210331
ABC	125	20210331
ABC	75	20201205
ABC	100	20210401
DEF	50	202103AA
DEF	125	202103BB
DEF	75	202012BB
DEF	500	202104AC

I need the total amount for col1 and the first row of the identifier as below in Oracle SQL

ID	Amt	Identifier
ABC	350	20210331
DEF	750	202103AA

Table structure:

ID varchar2

Amt Number

Idenfier varcahr2

Thanks in advance

What do you mean by "first" when you say "first row of the identifier"? (You almost surely mean "identifier value in the first row", there are no "rows of identifiers" in your table; but that still doesn't explain what first mean.) Note that in Gordon Linoff's answer, he just selects the least identifier - but that contradicts the (presumably desired) output you posted in your question. — user5683823
– user5683823, Commented Apr 2, 2021 at 6:28
I mean first the appearance of Identifier column value when the ID changes along with the SUM(Amt) — Ras Knorr
– Ras Knorr, Commented Apr 2, 2021 at 14:34
You don't seem to understand the question. When we display rows they appear in some (random!) order, because we can't show them on top of each other; but in a database, rows don't have a "natural" ordering within a given ID. There is no "first" or "second" or "third" appearance. "first" or "second" etc. when you order them by what? That's what I mean. Think of golf balls in a basket - which one is "first" or "second" etc.? You need to think about rows in a table the same way. They are ordered only when you specify an ordering. — user5683823
– user5683823, Commented Apr 2, 2021 at 16:06

Gordon Linoff · Accepted Answer · 2021-04-02 00:37:57Z

1

Are you looking for aggregation?

select id, sum(amt), min(identifier)
from t
group by id;

answered Apr 2, 2021 at 0:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user5683823 Over a year ago

min(identifier) will not produce the "desired output" the OP posted in his question. In fact what he shows is neither the min nor the max, in either case. It is "first row" (as he said in the question), in some undefined way. Did you notice that? If so, I don't see any mention of it in your reply. If you didn't see it - why not? Did you look?

Collectives™ on Stack Overflow

Summing UP rows in Oracle SQL query

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