CodeIgniter Forums
how to do this mysql task? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: how to do this mysql task? (/showthread.php?tid=79325)



how to do this mysql task? - richb201 - 05-28-2021

I have a table called survey_results_BC_temp. It has the following fields:
https://photos.google.com/photo/AF1QipMufvXBozoaKEbitLl2S7dqvvBDBPfpv9sCqWeM
I am trying to update the number_of_employees column. I have tried this:
UPDATE survey_results_BC_temp set number_of_employees=COUNT(distinct user_email) WHERE item=(SELECT item FROM survey_results_BC_temp)
But it doesn't work. 
Then I tried:
https://photos.google.com/photo/AF1QipMdDsjhBssa9NlnVekj-L9v5S21uP6Te7ruC9An

How can I fill in the number_of_employees field?


RE: how to do this mysql task? - InsiteFX - 05-29-2021

There you go with your cut copy and paste again messing up the code.


RE: how to do this mysql task? - richb201 - 05-29-2021

Yeah, I couldn't get this post correct and then the forum "went away" while i was typing it. I didn't know it posted. Sorry. I was trying to show the table I am working from.


RE: how to do this mysql task? - InsiteFX - 05-29-2021

If you need to copy cut and paste here is a little trick I found years ago, paste your text into an editor like Windows notepad then copy and paste it
It will remove all formatting characters.


RE: how to do this mysql task? - richb201 - 05-29-2021

Here is the sql:
$sql = "CREATE TABLE survey_results_BC_temp
SELECT taxyear,user_email, item, campaign, email, qualified
FROM survey_results sr
WHERE item_type='BC' and campaign=?";
I'd like to have another column called number of employees which has the number of distinct. So I tried this:

SELECT taxyear,user_email, item, campaign, email, qualified, COUNT(distinct user_email) as number_of_employees
FROM survey_results sr
WHERE item_type='BC' and campaign=?
GROUP BY item, user_email

When I try running this interactively in MYSQL workbench I get no response.