CODEWITHSUNDEEP
sqliteaveragegroup
Dave
Dave

Reputation: 11

How to interleave columns in sqlite

In SQLITE I have a table with three columns like so:

X Y Z
_____
1,2,A
1,3,B
3,4,C

I want what follows, order of the rows does not matter.

U Z
___
1 A
2 A
1 B
3 B
3 C
4 C

So then I can GROUP BY U and AVG(Z).

I tried a UNION but that is a bad way for me to go. I need something other than a UNION to get this done.

Upvotes: 0

Views: 40

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

In SQLite, a union might be the easiest way to achieve what you want here:

SELECT U, AVG(Z) AS Z_AVG
FROM (
    SELECT X AS U, Z FROM yourTable
    UNION ALL
    SELECT Y, Z FROM yourTable
) t
GROUP BY U
ORDER BY U;

Upvotes: 0

Related Questions