tekrscom Posted June 14, 2007 Share Posted June 14, 2007 Hi, can you not do a SUM() AS query and a regular query all in one? E.g. $query = mysql_query("SELECT *, SUM(MTES_direct_sub_task_hrs) AS direct_hours_total FROM MTES_direct_hours WHERE tech_id = '$_SESSION[userid]' && week_ending_date = '$_SESSION[week_ending_date]' && directontimesheet = '0'"); while ($row = mysql_fetch_array($query)) {} I want all the columns data as always, but I need to sum one particular column Quote Link to comment Share on other sites More sharing options...
jvrothjr Posted June 14, 2007 Share Posted June 14, 2007 i had the same issue i had to split it into two Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 I was hoping not to do that, but it looks like that is what I must do after all... thanks.... queries, queries, queries Quote Link to comment Share on other sites More sharing options...
joshi_v Posted June 14, 2007 Share Posted June 14, 2007 If you could use GROUP BY with query u can get what u want. Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 A GROUP BY and a SUM AS in one query? Would you happen to have a simple example of that? Quote Link to comment Share on other sites More sharing options...
joshi_v Posted June 14, 2007 Share Posted June 14, 2007 SELECT *, SUM(title)AS sum FROM suggest group by title This is what i have at this moment. Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 Honestly, I think my whole query is botched... Check it out. $i_query = mysql_query("SELECT * FROM MTES_direct_hours WHERE tech_id = '$_SESSION[userid]' && week_ending_date = '$_SESSION[week_ending_date]' && directontimesheet = '0'"); while ($i_row = mysql_fetch_array($i_query)) { echo ' <tr> <td style="color: RED;">', $i_row[gold_id], '</td> <td style="color: RED;">', $i_row[project], '</td> <td style="color: RED;">Direct</td> <td style="color: RED;">', $i_row[MTES_direct_task], '</td> <td style="color: RED;">', $i_row[week_ending_date_day], '</td> '; $z_query = mysql_query("SELECT SUM(MTES_direct_sub_task_hrs) AS direct_hours_total FROM MTES_direct_hours WHERE week_ending_date = '$i_row[week_ending_date]' && week_ending_date_day = '$i_row[week_ending_date_day]'"); while ($z_row = mysql_fetch_array($z_query)) { echo ' <td style="color: RED;">', $z_row[direct_hours_total], '</td> </tr> '; } $k_query = mysql_query("SELECT MTES_direct_sub_task, MTES_direct_sub_task_hrs FROM MTES_direct_hours WHERE direct_hours_id = '$i_row[direct_hours_id]'"); while ($k_row = mysql_fetch_array($k_query)) { echo ' <tr> <td style="color: RED;"> </td> <td style="color: RED;"> </td> <td style="color: RED;"> </td> <td style="color: RED;">', $k_row[MTES_direct_sub_task], '</td> <td style="color: RED;"> </td> <td style="color: RED;">', $k_row[MTES_direct_sub_task_hrs], '</td> </tr> '; } } It gives me all the data that I want, but it repeats the first query the amount of times that the third query has results... AHHHH! Quote Link to comment Share on other sites More sharing options...
joshi_v Posted June 14, 2007 Share Posted June 14, 2007 why are u repeating everything in the first while loop? i didn't get why? Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 Let me show the results, maybe that will clear it up. GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Unpack & inventory 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Bolt / Set cabinet 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Terminate ground cable to RBS 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Terminate AC /DC power cables to RBS 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Install & terminate alarm cable 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Install & Terminate Link Interfaces between DSX to 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Terminate Interconnecting Cabling & RF Tails 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Affix ALL labels as required 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Waste disposal 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Integrate BTS 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Preparation of site documents 1 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Quality Inspection 1 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Unpack & inventory 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Bolt / Set cabinet 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Terminate ground cable to RBS 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Terminate AC /DC power cables to RBS 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Install & terminate alarm cable 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Install & Terminate Link Interfaces between DSX to 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Terminate Interconnecting Cabling & RF Tails 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Affix ALL labels as required 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Waste disposal 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Integrate BTS 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Preparation of site documents 2 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Quality Inspection 2 You see, there are multiple sub-results for every result, the problem is that the main result is repeating, I only need the sub-result to repeat Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 This is how it should look... GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Unpack & inventory 1 Bolt / Set cabinet 1 Terminate ground cable to RBS 1 Terminate AC /DC power cables to RBS 1 Install & terminate alarm cable 1 Install & Terminate Link Interfaces between DSX to 1 Terminate Interconnecting Cabling & RF Tails 1 Affix ALL labels as required 1 Waste disposal 1 Integrate BTS 1 Preparation of site documents 1 Quality Inspection 1 GA2088 P1002 Direct Expansion - Installation & Integration Monday 24 Unpack & inventory 2 Bolt / Set cabinet 2 Terminate ground cable to RBS 2 Terminate AC /DC power cables to RBS 2 Install & terminate alarm cable 2 Install & Terminate Link Interfaces between DSX to 2 Terminate Interconnecting Cabling & RF Tails 2 Affix ALL labels as required 2 Waste disposal 2 Integrate BTS 2 Preparation of site documents 2 Quality Inspection 2 Quote Link to comment Share on other sites More sharing options...
joshi_v Posted June 14, 2007 Share Posted June 14, 2007 GA4101 P1002 Direct Expansion - Installation & Integration Monday 12 Which field in the code is displaying this? $i_row[project] ? Quote Link to comment Share on other sites More sharing options...
tekrscom Posted June 14, 2007 Author Share Posted June 14, 2007 $i_row[gold_id] $i_row[project] $i_row[MTES_direct_task] $i_row[week_ending_date_day] $z_row[direct_hours_total] Quote Link to comment Share on other sites More sharing options...
joshi_v Posted June 14, 2007 Share Posted June 14, 2007 $i_query = mysql_query("SELECT * FROM MTES_direct_hours WHERE tech_id = '$_SESSION[userid]' && week_ending_date = '$_SESSION[week_ending_date]' && directontimesheet = '0'"); while ($i_row = mysql_fetch_array($i_query)) { echo ' <tr> <td style="color: RED;">', $i_row[gold_id], '</td> <td style="color: RED;">', $i_row[project], '</td> <td style="color: RED;">Direct</td> <td style="color: RED;">', $i_row[MTES_direct_task], '</td> <td style="color: RED;">', $i_row[week_ending_date_day], '</td> '; $z_query = mysql_query("SELECT SUM(MTES_direct_sub_task_hrs) AS direct_hours_total FROM MTES_direct_hours WHERE week_ending_date = '$i_row[week_ending_date]' && week_ending_date_day = '$i_row[week_ending_date_day]'"); while ($z_row = mysql_fetch_array($z_query)) { echo ' <td style="color: RED;">', $z_row[direct_hours_total], '</td> </tr> '; } $k_query = mysql_query("SELECT MTES_direct_sub_task, MTES_direct_sub_task_hrs FROM MTES_direct_hours WHERE direct_hours_id = '$i_row[direct_hours_id]'"); while ($k_row = mysql_fetch_array($k_query)) { echo ' <tr> <td style="color: RED;"> </td> <td style="color: RED;"> </td> <td style="color: RED;"> </td> <td style="color: RED;">', $k_row[MTES_direct_sub_task], '</td> <td style="color: RED;"> </td> <td style="color: RED;">', $k_row[MTES_direct_sub_task_hrs], '</td> </tr> '; } } } Can u provide the result of this.. pls try it asap..time to leave 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.