CODEWITHSUNDEEP
sqloracle-databasewindow-functions
ZerOne
ZerOne

Reputation: 1326

SQL: partition over two columns

I have following table:

---------------------
| No1 | No2  | Amount
---------------------
| A   |  B   |    10 |
| C   |  D   |    20 |
| B   |  A   |    30 |
| D   |  C   |    40 |
---------------------

and I want to sum over partition by both columns (No1,No2) but it should group also when the values are changed in the two columns. Example would be: AB = BA

This would be my expected result:

-----------------------------------------
| No1 | No2  | Sum(Amount) over partition
-----------------------------------------
| A   |  B   |    40                    |
| C   |  D   |    60                    |
| B   |  A   |    40                    |
| D   |  C   |    60                    |
-----------------------------------------

Any ideas?

Upvotes: 3

Views: 10129

Answers (2)

Art
Art

Reputation: 5782

Just an idea:

WITH test_data AS
(
SELECT 'A' no1, 'B' no2, 10 amt FROM dual
UNION ALL
SELECT 'C', 'D', 20 FROM dual
UNION ALL
SELECT 'B', 'A', 30 FROM dual
UNION ALL
SELECT 'D', 'C', 40 FROM dual
)
SELECT NVL(no1, break_group) no1, no2, total --, grp1, grp2, break_group 
  FROM
(
SELECT no1, no2, total
     , grouping(no1) grp1
     , grouping(no2) grp2
     , LAG(no1, 2) OVER (PARTITION BY total ORDER BY no1)||LAG(no1) OVER (PARTITION BY total ORDER BY no1) break_group
  FROM
( -- Vamsi Prabhala query --
select no1, no2
     , sum(amt) over(partition by least(no1,no2),greatest(no1,no2)) as total
from test_data
)
GROUP BY ROLLUP (total, no1, no2)
HAVING grouping(no1) + grouping(no2) = 0
OR grouping(no1) + grouping(no2) = 2
AND total IS NOT NULL
)
/

Output - you can replace nulls (empty) with any vlue:

|NO1 |  NO2  | Total
----------------------
A        B      40
B        A      40
AB              40
C        D      60
D        C      60
CD              60

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use least and greatest.

select no1,no2,sum(amount) over(partition by least(no1,no2),greatest(no1,no2)) as total
from tbl

Upvotes: 6

Related Questions