Combine commands with subquery in SQLite
I have a SQLite table that looks like this:
ID_TABLE POINTS_A_TABLE POINTS_B_TABLE
id number id_a points_a id_b points_b
-------------- ---------------- ----------------
smith 1 smith 11 smith 25
gordon 22 gordon 11 gordon NULL
butch 3 butch 11 butch 26
sparrow 25 sparrow NULL sparrow 44
white 76 white 46 white NULL
With the following command
SELECT id,
avg(points_a)
FROM (SELECT id_a AS id, points_a FROM points_a_table
UNION ALL
SELECT id_b AS id, points_b FROM points_b_table)
GROUP BY id
ORDER BY avg(points_a) DESC;
I can get the average of the scores associated with each name ( more details here )
id avg(points_a)
white 46.0 [(46+0)/2]
sparrow 44.0 [(0+44)/2]
butch 18.5 [(11+26)/2]
smith 18.0 [(11+25)/2]
gordon 11.0 [(11+0)/2]
Now I need to map the resulting column id
to the corresponding column number
in ID_TABLE
with ID_TABLE.number LESS THAN 26 . The result should be ( number|average
):
76 46.0 [(46 + 0) / 2]
25 44.0 [(0+44)/2]
3 18.5 [(11+26)/2]
76 18.0 [(11+25)/2]
22 11.0 [(11+0)/2]
How can I accomplish everything in one request using a combination of the new instructions with the previous ones?
source to share
You need to do a JOIN and then change your grouping slightly to keep the aggregate function working. Assuming id_table
there is exactly one entry for each matching entry in points_a
or points_b
:
SELECT i.number,
avg(pts.points) AS average_points
FROM (SELECT id_a AS id, points_a AS points FROM points_a_table
UNION ALL
SELECT id_b AS id, points_b AS points FROM points_b_table) AS pts
INNER JOIN id_table i ON i.id = pts.id
GROUP BY pts.id, i.number
WHERE i.number < 26
ORDER BY avg(pts.points) DESC;
source to share
It looks simple, using the original query as a subquery. Doesn't that give you what you want?
SELECT
idt.number,
avg_points
FROM
id_table AS idt
INNER JOIN (
SELECT id,
avg(points_a) AS avg_points
FROM (SELECT id_a AS id, points_a FROM points_a_table
UNION ALL
SELECT id_b AS id, points_b FROM points_b_table)
GROUP BY id
) as x on x.id=idt.id
WHERE
idt.number < 26;
source to share