tobeyt23 Posted June 3, 2008 Share Posted June 3, 2008 trying to calculate total minutes used correctly, obviously if you add the totals the for the minutes it is incorrect what am i doing incorrect? ( SELECT IF( voip_log.direct_dial = 'true', 'Direct Dial', IF( voip_log.voiceblast =1, 'Voice Broadcast', 'PhoneLink' ) ) Call_Type, count( voip_log.id ) Total_Calls, CEIL( SUM( unix_timestamp( STR_TO_DATE( callendtime, '%m/%d/%Y %r' ) ) - unix_timestamp( STR_TO_DATE( callconnecttime, '%m/%d/%Y %r' ) ) ) /60 ) Total_Minutes_Used FROM icar.voip_log, icar.dealer WHERE voip_log.dealerID = dealer.id AND voip_log.callendtime >0 AND voip_log.status = 'complete' AND voip_log.timestamp BETWEEN '1207022400' AND '1209614399' GROUP BY Call_Type ) UNION ALL ( SELECT 'Total'Call_Type, count( voip_log.id ) Total_Calls, CEIL( SUM( unix_timestamp( STR_TO_DATE( callendtime, '%m/%d/%Y %r' ) ) - unix_timestamp( STR_TO_DATE( callconnecttime, '%m/%d/%Y %r' ) ) ) /60 ) Total_Minutes_Used FROM icar.voip_log, icar.dealer WHERE voip_log.dealerID = dealer.id AND voip_log.callendtime >0 AND voip_log.status = 'complete' AND voip_log.timestamp BETWEEN '1207022400' AND '1209614399' GROUP BY Call_Type ) this is what is returned: Call_Type Total_Calls Total_Minutes_Used Direct Dial 24045 51814 PhoneLink 1 1 Voice Broadcast 24 14 Total 24070 51828 Quote Link to comment Share on other sites More sharing options...
fenway Posted June 3, 2008 Share Posted June 3, 2008 Do the inidivudla queries work? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 3, 2008 Share Posted June 3, 2008 And consider the reduction in effort on your part if you were to store dates correctly in ISO DATETIME fields. Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted June 4, 2008 Author Share Posted June 4, 2008 Yes they both work individually and the db was created before my time and I am working with it as is for now. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 4, 2008 Share Posted June 4, 2008 Show us the output from each individual query, then the combined query output so we can see what you actually want and what you're actually getting. Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted June 4, 2008 Author Share Posted June 4, 2008 First select: Call_Type Total_Calls Total_Minutes_Used Direct Dial 24045 51814 PhoneLink 1 1 Voice Broadcast 24 14 Second select: Call_Type Total_Calls Total_Minutes_Used Total 24070 51828 Orginal: Call_Type Total_Calls Total_Minutes_Used Direct Dial 24045 51814 PhoneLink 1 1 Voice Broadcast 24 14 Total 24070 51828 Quote Link to comment Share on other sites More sharing options...
fenway Posted June 4, 2008 Share Posted June 4, 2008 Interesting... have you tried GROUP BY... WITH ROLLUP? Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted June 4, 2008 Author Share Posted June 4, 2008 No i have not, never used ROLLUP Quote Link to comment Share on other sites More sharing options...
tobeyt23 Posted June 5, 2008 Author Share Posted June 5, 2008 Nope tried it same results Quote Link to comment Share on other sites More sharing options...
fenway Posted June 5, 2008 Share Posted June 5, 2008 I think I know the problem... it's your CEIL() function... you're doing the rounding each time for the individual counts, but then once overall for the total, so you're getting different results. 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.