SQL - Is it possible to print all records or only one record taking into account count(*) in a table?

user_11726464 0
Viewed 148

I am trying to find a way to print the result taking into account if exists records in a table or not. If yes I should print all the records in a table, otherwise I should print only one record.

Example:

I have the Table Example:

ColA Colb ColC

If select count(*) from Example > 0 THEN Return

ColA Colb ColC
1 VA1 NULL
2 VB1 NULL
3 NULL VA2

If select count(*) from Example <= 0 THEN Return

ColA Colb ColC
Result NA NA

Is it possible to do something like that? I am doing the development using PRESTO.

Thanks you in advance

presto sql
2 Answers

Try:

SELECT ColA,ColB,ColC
FROM Example
union
SELECT 
   'Result' as ColA,
   'NA' as ColB,
   'NA' as ColC
WHERE NOT EXISTS (SELECT ColA,ColB,ColC
                  FROM Example)

It is not needed to select the 3 columns in the sub-query, but currently I copy/pasted the query. It can be replaced by SELECT 1 or SELECT NULL (which is a matter of taste...)

user_7240390

We could introduce a dummy/default row via a union, and then retain it only in the event of the Example table being empty:

 WITH cte AS (
    SELECT ColA, ColB, ColC, 0 AS pos FROM Example
    UNION ALL
    SELECT 'Result', 'NA, 'NA', 1
)

SELECT ColA, ColB, ColC
FROM cte
WHERE pos = 0 OR NOT EXISTS (SELECT 1 FROM cte WHERE pos = 0);
user_18632290
Related