CODEWITHSUNDEEP
sqloracle-databasedata-cleaning
StackOne
StackOne

Reputation: 286

Filtering Rows in SQL

My data looks like this: Number(String), Number2(String), Transaction Type(String), Cost(Integer)

enter image description here

enter image description here

For number 1, Cost 10 and -10 cancel out so the remaining cost is 100

For number 2, Cost 50 and -50 cancel out, Cost 87 and -87 cancel out

For number 3, Cost remains 274

For number 4, Cost 316 and -316 cancel out, 313 remains as the cost

The output I am looking for Looks like this: enter image description here

How do I do this in SQL?

I have tried "sum(price)" and group by "number", but oracle doesn't let me get results because of other columns

https://datascience.stackexchange.com/questions/47572/filtering-unique-row-values-in-sql

Upvotes: 0

Views: 79

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Oracle has very nice functionality equivalent toe first() . . . but the syntax is a little cumbersome:

select number,
       max(number2) keep (dense_rank first order by cost desc) as number2,
       max(transaction_type) keep (dense_rank first order by cost desc) as transaction_type,
       max(cost) as cost
from t
group by number;

In my experience, keep has good performance characteristics.

Upvotes: 0

kfinity
kfinity

Reputation: 9091

When you're doing an aggregate query, you have to pick one value for each column - either by including it in the group by, or wrapping it in an aggregate function.

It's not clear what you want to display for columns 2 and 3 in your output, but from your example data it looks like you're taking the MAX, so that's what I did here.

select number, max(number2), max(transaction_type), sum(cost)
from my_data
group by number
having sum(cost) <> 0;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use correlated subquery :

select t.*
from table t
where t.cost = (select sum(t1.cost) from table t1 where t1.number = t.number);

Upvotes: 0

stickabee
stickabee

Reputation: 145

You're almost there... you'll need to get the sum for each number without the other columns and then join back to your table.

select * from table t
join
(select number,sum(cost)
from table
group by number) sums on sums.number=t.number

Upvotes: 0

Related Questions