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

mysql get count(column) if first select query return no result

I want to execute 2nd query if first query return no result. I want to get those id age > 20. If no result, want to get number of id with age < 20.

In table:

   id   name   gender   age
    1   'Ryan'  'M'     30
    2  'Joanna' 'F'     10

query

(SELECT id FROM students WHERE age > 20
ORDER BY id desc limit 1)

UNION ALL

SELECT count(id) FROM students WHERE age < 20 AND 
NOT EXISTS (SELECT * FROM students WHERE age > 20  ORDER BY id desc limit 1)

result :

   id 
    1 
    0

It should return 1 only but return 1 and 0.

If change ryan age to 10, it return 2 which is correct.

Link :query

Tags:

2

Answers


  1. 0

    UNION ALL is conjunction of queries in terms of running all queries before and after the keyword.

    your request could be done by using stored procedure where you can add condition to check if it needs to execute the second query.

    Login or Signup to reply.
  2. 0

    Here you go;
    As you asked for case 1: list of id’s limit 1 and case 2: count.
    This should work!!!

    -- fetch some values
With CTE_above20 AS
(   SELECT 
        'Ids of age above 20' Description, id 
    FROM students WHERE age > 20 ORDER BY id desc limit 1 
), CTE_under20 AS
(   SELECT
        'Count of ids age below 20' Description
        , CASE WHEN  (SELECT count(1) FROM CTE_above20 LIMIT 1) > 0 THEN NULL ELSE count(id) END as id 
    FROM students WHERE age < 20 )
SELECT * FROM CTE_above20
UNION ALL
SELECT * FROM CTE_under20 
WHERE id is NOT  NULL

    Result 1: Insert statement with age > 20 and one for age < 20

    -- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',30);
INSERT INTO students VALUES (2, 'Joanna', 'F',40);
INSERT INTO students VALUES (3, 'Ryan', 'M',1);


 /*  Output 1
        Description             Id
        --------------------------
        Ids of age above 20     2
        
*/

    Result 2 All inserts which age < 20

    -- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M',10);
INSERT INTO students VALUES (2, 'Joanna', 'F',10);
INSERT INTO students VALUES (3, 'Ryan', 'M',1);

/*

Description                 Id
------------------------------
Count of ids age below 20   3
*/
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search