Danny Danny - 5 months ago 6
SQL Question

Sql - write something instead of NULL values

I am doing a query like this:

select name, count(*)as num from client, work where client.id = work.idc group by name;


And i am getting result of 2 columns,
name
and
num
which i see the name and the sum of how many times each one of the names used something.

I have also clients that did nothing.

What i want to do is showing them also in the new table and instead of writing their count (which is 0 because they still did nothing) i want to write in the same column
num
, "still did nothing".

I heard i can do it with
if
or
case
but i have no clue how.

Answer

you need a left join to get names with no work
you need count(work.idc) for 0

select name
     , case count(work.idc)  when 0  then 'still did nothing' 
                             else cast (count(work.idc) as varchar2 (50)) end  as num
from client  
left join work 
on client.id = work.idc 
group by name;

try this

select name
     , count(work.idc) as num
from client  
left join work 
on client.id = work.idc 
group by name;