I have this query with multiple subqueries and I got an error message "Every derived table must have its own alias".
SELECT SUM(set1 set2 set3)
FROM (
SELECT COUNT(b.`Set1-p1`) as set1 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set1-p1` > b.`Set1-p2` && g.PlayerOneID = 12 (
SELECT COUNT(b.`Set2-p1`) as set2 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set2-p1` > b.`Set2-p2` && g.PlayerOneID = 12 (
SELECT COUNT(b.`Set3-p1`) as set3 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set3-p1` > b.`Set3-p2` && g.PlayerOneID = 12 )
)
)
I tried to name the tables at the end but then I got this error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS T1
) AS T2
) AS T3
) AS T4
LIMIT 0, 50' at line 16"
SELECT SUM(set1 set2 set3)
FROM (
SELECT COUNT(b.`Set1-p1`) as set1 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set1-p1` > b.`Set1-p2` && g.PlayerOneID = 12 (
SELECT COUNT(b.`Set2-p1`) as set2 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set2-p1` > b.`Set2-p2` && g.PlayerOneID = 12 (
SELECT COUNT(b.`Set3-p1`) as set3 FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set3-p1` > b.`Set3-p2` && g.PlayerOneID = 12 AS `T1`) AS `T2`
) AS `T3`
) AS `T4`
CodePudding user response:
If you want to run three queries, each returning a single numeric result, and then add them up, yielding a single number:
SELECT
(
SELECT COUNT(b.`Set1-p1`) as set1
FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set1-p1` > b.`Set1-p2`
AND g.PlayerOneID = 12
)
(
SELECT COUNT(b.`Set2-p1`) as set2
FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set2-p1` > b.`Set2-p2`
AND g.PlayerOneID = 12
)
(
SELECT COUNT(b.`Set3-p1`) as set3
FROM synbadminton as b
INNER JOIN syngame as g
ON b.GameID = g.GameID
WHERE b.`Set3-p1` > b.`Set3-p2`
AND g.PlayerOneID = 12
)