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?
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;
/
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;
BY in GROUP BY was optional ;-)
number_peoplecolumn, It's not clear what number of people do you want to count from this table usingSELECT count(number_people) FROM dual?