## Issue

Suppose I’ve a table with like this:

```
+---+---+---+---+---+
|G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+
| A | B | C | D | 2 |
| E | F | G | H | 4 |
| C | D | A | B | 2 |
| E | F | G | H | 3 |
| E | I | G | L | 7 |
+---+---+---+---+---+
```

Where G1, G3 are VARCHAR and G2, G4 are INT

If I do a simple GROUP BY on G1..G4 I get:

```
SELECT G1,G2,G3,G4,SUM(V) as V FROM Table GROUP BY G1, G2, G3, G4;
+---+---+---+---+---+
|G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+
| A | B | C | D | 2 |
| E | F | G | H | 7 |
| C | D | A | B | 2 |
| E | I | G | L | 7 |
+---+---+---+---+---+
```

I’m wondering if is possible to aggregate on inverted value G1,G2 <-> G3,G4.

What I want to achieve is a result like this:

```
+---+---+---+---+---+ +---+---+---+---+---+
|G1 |G2 |G3 |G4 | V | |G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+ +---+---+---+---+---+
| A | B | C | D | 4 | OR | C | D | A | B | 4 |
| E | F | G | H | 7 | | E | F | G | H | 7 |
| E | I | G | L | 7 | | E | I | G | L | 7 |
+---+---+---+---+---+ +---+---+---+---+---+
```

What I tried is to get the inverted rows, but I still can’t aggregate on them due to repetition. My test query is:

```
SELECT DISTINCT *
FROM Table t1
JOIN Table t2 on t1.G1 = t2.G3 and t1.G2=t2.G4 and t1.G3=t2.G1 and t1.G4=t2.G2
+---+---+---+---+---+---+---+---+---+---+
|G1 |G2 |G3 |G4 | V |G1 |G2 |G3 |G4 | V |
+---+---+---+---+---+---+---+---+---+---+
| A | B | C | D | 2 | C | D | A | B | 2 |
| C | D | A | B | 2 | A | B | C | D | 2 |
+---+---+---+---+---+---+---+---+---+---+
```

## Solution

If I’ve got it right, reverse the order of columns so that A B C D matches C D A B .

```
select
case when(g1 > g3) then g3 else g1 end G1,
case when(g1 > g3) then g4 else g2 end G2,
case when(g1 > g3) then g1 else g3 end G3,
case when(g1 > g3) then g2 else g4 end G4, sum(V)
from tbl
group by case when(g1 > g3) then g3 else g1 end,
case when(g1 > g3) then g4 else g2 end,
case when(g1 > g3) then g1 else g3 end,
case when(g1 > g3) then g2 else g4 end
```

Answered By – Serg

Answer Checked By – Timothy Miller (BugsFixing Admin)