CODEWITHSUNDEEP
phpsql
Jan Leon
Jan Leon

Reputation: 176

How to do a double select statement in one query SQL

It is possible to do a double select statement into one query, and this show each value individually, using sum and suma for the first select I'm expecting only one value, and for the second one 4 values.

echo ' ' . htmlentities($rowsm['sum'], ENT_QUOTES, 'UTF-8') . '<br>'; 
$query = "SELECT SUM(a.totalpoints) as sum
          FROM total as a
          WHERE a.id = '$id'
          GROUP BY a.id
          UNION ALL

SELECT SUM(s.points) as suma
       FROM points as s 
       GROUP BY s.id

This is the output I'm getting

A 21 first select
A 11 second select
A 10 second select
A 9 second select
A 14 second select

21 Total sum

A 11 points
A 10 points
A 9 points
A 14 points

Upvotes: 0

Views: 1970

Answers (2)

Aram
Aram

Reputation: 379

You cannot call more than one select statement in one query unless it's a transaction. Change your query to this:

$query = "
    START TRANSACTION;
    SELECT SUM(a.totalpoints) as suma
              FROM total as a
              WHERE a.id = '$id'
              GROUP BY a.id
              UNION ALL

    SELECT SUM(s.points) as suma
           FROM points as s 
           GROUP BY s.id
    COMMIT;";

Also as @Swagata mentioned you cannot use keyword as an alias

Upvotes: 0

Swagata
Swagata

Reputation: 622

In UNION ALL, the corresponding fields need to have the same name, otherwise how will the system decide which name to display in the column header? Your SQL needs to be of the form:

SELECT SUM(a.totalpoints) as suma
      FROM total as a
      WHERE a.id = '$id'
      GROUP BY a.id
      UNION ALL

SELECT SUM(s.points) as suma
   FROM points as s 
   GROUP BY s.id

Also, sum is a keyword, so you can not use an alias named sum. i.e. "as sum" is probably not valid.

Upvotes: 1

Related Questions