0

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.

Danny
  • 121
  • 1
  • 2
  • 10

3 Answers3

1

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;
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • Hi, thank you for your answer. I tried it but for some reason in the num colums, instead of numbers counted it shows some "blob" string/image, I don't know exactly what it is. – Danny Jun 11 '16 at 14:50
  • Wow - try without the case – paparazzo Jun 11 '16 at 14:54
  • but when i am doing the second option you gave without the case i am not getting here the 'still did nothing', only the results not 0. – Danny Jun 11 '16 at 15:19
  • i mean, i am getting the 0 but not 'still did nothing' instead of the 0. – Danny Jun 11 '16 at 15:21
  • Yes I am aware the second case would return a 0. I think I have done the hard part and it feels like you are not giving this much effort. – paparazzo Jun 11 '16 at 15:31
  • you are totally right...:) trying the rest by myself. – Danny Jun 11 '16 at 16:07
0

You would also need to change it to a left outer join and do something like this (not sure if syntax is exact for mysql):

select 
  name, 
  Case when count(*) =0
       Then 'still did nothing'
       Else cast (count(*) as varchar2 (50))
  End as num
from client left outer join work 
On client.id = work.idc
Group by name;
msheikh25
  • 576
  • 3
  • 9
0

You can try if or case like this:

select name, if (clientWork = 0, 'still did nothing', clientWork) as num from (select name, count(work.idc) as clientWork from client left join work where client.id = work.idc group by name) as Client_Work;

or

select name, case when clientWork = 0 then 'still did nothing' else clientWork end as num from (select name, count(work.idc) as clientWork from client left join work where client.id = work.idc group by name) as Client_Work;
Yash Vyas
  • 558
  • 4
  • 24