Question 20005 in Mysql Question posted in
The official MySQL documentation can be found here.

Create a list in MySQL without data from a table?

Is it possible to create a list with a select statement in MySQL, without fetching data from a table? Let’s say I want to use an aggregate function to calculate a sum of some values not fetched from a table.

You can use SELECT (SELECT 1) + (SELECT 1) to add 1+1. But what if I want to add the numbers 3, 8, 9, 7 with the SUM-function.

I tried some different variants like SELECT SUM(*) FROM (SELECT 3, 8, 9, 7) and SELECT SUM((SELECT 3, 8, 9 ,7)), but that obviously doesn’t work.

This really doesn’t have any practically use for me. I’m just curious and trying to understand SQL on a "deeper" level.

Tags:

2

Answers


  1. 0

    You can use the VALUES keyword to create a virtual table with the specified values, and then use aggregate functions like SUM to perform calculations on the data.

    To add the numbers 3, 8, 9, and 7, you can use the following query:

    SELECT SUM(value) FROM (VALUES (3), (8), (9), (7)) AS t(value);

    This creates a virtual table called "t" with a single column called "value" and four rows containing the specified values. The SUM function is then applied to the "value" column to calculate the total sum.

    You can use this technique to create lists of any type of data, not just numbers. For example, you can create a list of strings like this:

    SELECT GROUP_CONCAT(value SEPARATOR ', ') FROM (VALUES ('apple'), ('banana'), ('cherry')) AS t(value);

    This creates a virtual table with three rows, each containing a single string value. The GROUP_CONCAT function is then used to concatenate the values into a single string with commas separating them.

    Hope this helps. 🙂

    Login or Signup to reply.
  2. 0 
    SELECT SUM(num) FROM (
  VALUES ROW(3), ROW(8), ROW(9), ROW(7)
) AS t(num);

    Output:

    +----------+
| SUM(num) |
+----------+
|       27 |
+----------+

    The VALUES statement is supported in MySQL 8.0.19 and later.

    If you use an older version of MySQL you can do something similar with UNION:

    SELECT SUM(num) FROM (
  SELECT 3 AS num
  UNION SELECT 8
  UNION SELECT 9
  UNION SELECT 7
) AS t;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search