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

Question

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

Answer 1

Here you go; As you asked for case 1: id age > 20 limit 1 and case 2: count of age < 20 only if there is no resultset from case 1.

This should work!!!

-- fetch some values
With CTE_above20 AS
(   SELECT 
        'Descending Top 1 id 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
        --------------------------------------
        Descending Top 1 id 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
*/