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 Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/ Share on other sites More sharing options...
fenway Posted June 3, 2008 Share Posted June 3, 2008 Do the inidivudla queries work? Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-556664 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. Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557001 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. Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557360 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. Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557569 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 Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557619 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? Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557662 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 Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557716 Share on other sites More sharing options...
tobeyt23 Posted June 5, 2008 Author Share Posted June 5, 2008 Nope tried it same results Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-557999 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. Link to comment https://forums.phpfreaks.com/topic/108545-math-not-adding-up/#findComment-558315 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.