I am having trouble with a SQLite query and I've tried googling it over the last few days, but I don't even know what the operation is called to come up with relevant answers.
I have a pretty standard SQLite table with 10 columns, but only 3 are important here - MS (milliseconds), Version, Buildings
What I am trying todo is create a table that looks like this -
| Buildings | Version1 | Version2 | Version3 | Version4 | Version5 | etc... |
|---|---|---|---|---|---|---|
| 1 | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) |
| 2 | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) |
| 3 | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) | avg(MS) |
The tricky part is that there are an unknown number of versions and even the version names themselves are unknown. What I have been using is something like this -
SELECT avg(ms), version, buildings From loadtimes group by buildings, version
which creates a table like this -
| avg(ms) | version | buildings |
|---|---|---|
| 39527 | 22.5.1-0.4 | 1 |
| 31150 | 22.5.2-1.1 | 1 |
| 65833 | 22.5.1-0.4 | 2 |
| 76612 | 22.5.2-1.1 | 2 |
| 59430 | 22.5.1-0.4 | 3 |
| 105924 | 22.5.2-1.1 | 3 |
Which does give me the information I need, just not in the format where I can graph it.
What is this type of operation called? and how do I create a table with multiple unknown columns?
Any help at all would be greatly appreciated. Thank You.
CodePudding user response：
If you can hardcode the versions, then use conditional aggregation:
SELECT buildings,
AVG(CASE WHEN version = '22.5.1-0.4' THEN ms END) AS "Version 22.5.1-0.4",
AVG(CASE WHEN version = '22.5.2-1.1' THEN ms END) AS "Version 22.5.2-1.1"
........................................................
FROM loadtimes
GROUP BY buildings;
See the demo.