inbowns Posted December 28, 2011 Share Posted December 28, 2011 I'm working on a database what I would like to do is find out of the number of total number of employees that migrated I would only like to find how many migrated. For example there 5 cases there are a 95 total employees out of them 3 cases migrated with a total of 65 migrates. I would like to know how can I get this to work with my existing SQL statement. $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; Any help is greatly appreciated. Thanks in advance. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted December 29, 2011 Share Posted December 29, 2011 This should work: SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , (( SELECT count(employees) FROM info WHERE migrate = 'yes')) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30 Quote Link to comment Share on other sites More sharing options...
inbowns Posted December 30, 2011 Author Share Posted December 30, 2011 Thanks for replying but it doesn't work it gives me a weird number. quote author=Muddy_Funster link=topic=350723.msg1655562#msg1655562 date=1325163381] This should work: SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , (( SELECT count(employees) FROM info WHERE migrate = 'yes')) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted December 30, 2011 Share Posted December 30, 2011 try changing count(employees) to count(*) what exactly is a Wierd number? Quote Link to comment Share on other sites More sharing options...
inbowns Posted December 30, 2011 Author Share Posted December 30, 2011 I changed it but I'm get 451 from the database and i don't have 451 employees listed in that date range. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted December 30, 2011 Share Posted December 30, 2011 ahh, that's because the date range isn't being applied to the migrate count() - that's everyone in the table. just append the WHERE within the brackets to include your daterange condition aswell Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 30, 2011 Share Posted December 30, 2011 I'm working on a database what I would like to do is find out of the number of total number of employees that migrated I would only like to find how many migrated. For example there 5 cases there are a 95 total employees out of them 3 cases migrated with a total of 65 migrates. I would like to know how can I get this to work with my existing SQL statement. $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; Any help is greatly appreciated. Thanks in advance. The explanation of your apparent objectives (in bold) is really not clear to me, could you explain it better?.. an example showing part of your real data and the expected result will help to offer you an answer Quote Link to comment Share on other sites More sharing options...
inbowns Posted December 30, 2011 Author Share Posted December 30, 2011 Mikoskio in the picture below this is what I currently have do you see where "Employees" are listed well out of those i need to get a total of how many employees of those were yes when selected in the database. I currently have all of the employees from all the carriers regardless if it was yes or no. So what I need is a new column that would show migrated(employees) Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 30, 2011 Share Posted December 30, 2011 and what represent your column "Migrated" actually? or that is the column that you want to obtain? and post the exact SELECT that you have today to produce the result that you shown Quote Link to comment Share on other sites More sharing options...
inbowns Posted December 30, 2011 Author Share Posted December 30, 2011 In the database there's a migrated column that accepts Yes or No. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 30, 2011 Share Posted December 30, 2011 I understood that.... you didn't answer my question nor provided the required information ... asking again: and what represent your column "Migrated" actually? (here I'm asking regarding to the picture that you provide) or that is the column that you want to obtain? and post the exact SELECT that you have today to produce the result that you shown Quote Link to comment Share on other sites More sharing options...
inbowns Posted December 30, 2011 Author Share Posted December 30, 2011 This is the SQL statement I'm using today that produced that picture $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; It should look like this carrier Case employees migrated #of migrate employees Aetna 15 6 3 2 HIP 7 71 4 23 Oxford 25 10 1 3 Do you see from the overall total of employees I need to get how many migrated out of the total. I hope this is clear. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted December 31, 2011 Share Posted December 31, 2011 I don't follow... your SUM(IF(migrate='YES',1,0)) is doing exactly that... is not clear from the select that you shown how you produce the output... $sql= "SELECT effdate, carrier, COUNT(carrier), SUM(employees), COUNT(employees) , SUM(IF(migrate = 'Yes', 1, 0)) AS 'migrate_yes' FROM info WHERE effdate = '$date_search_8' GROUP BY carrier WITH ROLLUP LIMIT 0 , 30"; - effdate is not shown in you final output and anyway it will not have any meaning at all because your GROUP BY. - carrier ... OK... it is shown. - COUNT(carrier)... is this the column 'Case' ?? - SUM(employees) ... is this your column 'Employees'?... what are you summing ?... the employees Id's? - COUNT(employees)... what column is this in your output?... seems logical to me that it should be your column 'employees' in your output. - SUM(IF...)) this is the column 'Migrated'?... should be.. an it represent the number of migrated employees for a carrier (the ones that have a 'YES' in the column Migrated in your table your last column '# of migrated employees' doesn't make sense to me... I do not understand from your explanations what that could be... maybe it make sense for someone else. Quote Link to comment Share on other sites More sharing options...
inbowns Posted January 3, 2012 Author Share Posted January 3, 2012 Thanks to everyone that attempted to help me with this problem. I guess sometimes it's best to grab the bull by the horns and try to wrestle it down yourself. This has been offically solved. By plugging this into my SQL statement it gave me the difference of the employee total and the employee total that actually migrated. I hope this helps someone in the future. (SUM(employees)- SUM(CASE WHEN migrate = 'Yes' THEN 0 ELSE employees END)) AS 'migrate_total' FROM info Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.