Menu
Suppose I had this following table in SQL Server:
ColA ColB ColC
A 1 100
A 0 -100
A 1 -100
A 0 100
A 1 100
A 1 -100
B 1 200
B 1 -200
B 1 200
C 0 300
C 1 -300
And what I was looking to achieve (in words) woud be:
Create a new column, Conditional_Sum = Sum of ColC for each value in ColA where ColB = 1 and ColC > 0
So, the final result would be (added in RowNum to show how I got the values):
Rownum ColA ColB ColC Conditional_Sum
1 A 1 100 200 (rows 1+5)
2 A 0 -100 200 (rows 1+5)
3 A 1 -100 200 (rows 1+5)
4 A 0 100 200 (rows 1+5)
5 A 1 100 200 (rows 1+5)
6 A 1 -100 200 (rows 1+5)
7 B 1 200 400 (rows 7+9)
8 B 1 -200 400 (rows 7+9)
9 B 1 200 400 (rows 7+9)
10 C 0 300 0 (no rows match)
11 C 1 -300 0 (no rows match)
So, my pseudo-SQL for this would be:
Sum(ColC) Over(Partition By ColA, ColB=1,ColC>0) as Conditional_Sum
How can I achieve this kind of result in my dataset??
Thanks!!!!!!
871
We can use the SQL PARTITION BY clause with the OVER clause to specify the column on which we need to perform aggregation. In the previous example, we used Group By with CustomerCity column and calculated average, minimum and maximum values.
SQL SUM() with where clause We can selectively find the sum of only those rows, which satisfy the given condition. To do this, we can use the where clause in the SQL statement.
For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the WHERE clause that restricts the retrieval to one or more partitions of table. Oracle Database will interpret the condition and fetch data from only those partitions.
Try this:
Sum(case when ColB = 1 and ColC > 0 then ColC else 0 end) over(partition by ColA)
My answer assumes that the conditional sum for rows 7 to 9 is supposed to be 400.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
