CODEWITHSUNDEEP
mysql
nrmad
nrmad

Reputation: 471

Double alias required for mysql query to function

I was working with a query which used a UNION to count the number of records in two tables which respectively match on a single id. Each subquery with its count was assigned an alias c so that the outer SUM(c) select statement could add the returned subquery counts. The query is pictured below: 

SELECT SUM(c) AS a1
FROM (
  SELECT COUNT(*) AS c FROM MCQuestion WHERE quizID = 120
  UNION ALL
  SELECT COUNT(*) AS c FROM InputQuestion WHERE quizID = 120
) AS a2;

I managed to get the query to function by adding as second alias "a1" directly after SUM(c) wherein the alias a1 is taken. However without the second alias "a2" the query fails with the error:

every derived table must have its own alias

If I omit alias a1 but retain a2 the query runs but ignores a2 and calls the table "SUM(c)" is it just that the subquery in brackets is treated at its own field even though I would've thought that it being used as a table because of the "FROM" clause that this would be uneccesarry.

Thanks

Upvotes: 2

Views: 213

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

The explanation is simple

a2 is the (mandatory) table name.

for each FROM ( subquery ) is mandatory assigne a "derived table" name .. in this case the table name assigne is a2.

a1 is the column alias for sum(c)

you could also use sum(a2.c) do the fatc that c come for the table ( from subquery ) named a2 

SELECT SUM(c) AS a1 
FROM (
    SELECT COUNT(*) AS c 
    FROM MCQuestion 
    WHERE quizID = 120 
    UNION ALL 
    SELECT COUNT(*) 
    FROM InputQuestion 
    WHERE quizID = 120
)  a2
;

Upvotes: 2

Related Questions