2

i want to use a COUNT result as IF CONDITION on ORACLE, like this pseudocode on oracle:

select count(d.number_people) as counted_people
from dual d
if counted_people = 1 then
dbms_output.put_line('Have just one people on this column.'
end if;

This syntax is wrong, I know, how do I do it the right way?

Matheus Isac
  • 25
  • 5
  • [DUAL](https://en.wikipedia.org/wiki/DUAL_table) is a special one-row one-colum table, and it does not have `number_people` column, It's not clear what number of people do you want to count from this table using `SELECT count(number_people) FROM dual` ? – krokodilko Dec 18 '18 at 19:58
  • Its a example , the ploblem is how to use a count as if statement – Matheus Isac Dec 18 '18 at 20:00
  • 2
    Possible duplicate of [if (select count(column) from table) > 0 then](https://stackoverflow.com/questions/10200281/if-select-countcolumn-from-table-0-then) – rs. Dec 18 '18 at 20:07

2 Answers2

1

Use SELECT ... INTO variable ...

Demo: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=610d02797857539be5fcd4afdbd7d9e6

DECLARE 
  cnt number;
BEGIN
  select count(*) INTO cnt from dual;
  IF cnt = 1 THEN
     DBMS_OUTPUT.PUT_LINE('Exaclty one record');
  ELSE
     DBMS_OUTPUT.PUT_LINE('0 or more than 1  record');
  END IF;
END;
/
krokodilko
  • 35,300
  • 7
  • 55
  • 79
1

COUNT is an aggregation result. You can count all rows in a table, all rows that match certain criteria, etc. As an aggregation result, you can use it in the SELECT clause, in the HAVING clause, and in the ORDER BY clause. (You cannot use it in the WHERE clause, because the WHERE clause deals with the data in the table rows. You need a HAVING clause instead, which deals with aggregation results.)

SELECT clause / ORDER BY clause example

select
  supplier_id,
  case when count(*) < 20 then 'less then 20 orders'
       when count(*) < 40 then 'less beteen 20 and 40 orders'
       else '40 or more orders'
  end as number_of_orders
from orders
where order_date >= date '2018-01-01'
group by supplier_id
order by count(*) desc;

HAVING clause example

select sex, count(*)
from members
group by sex
having count(*) > 20;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73