Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Division in MySQL

Posted on 2015-01-28
6
Medium Priority
?
309 Views
Last Modified: 2015-01-28
I have a MySQL query and I cant get the average right. I am dividing the total by the number of days and it is giving me a wierd number
Anyone see the issue?
SELECT job.company, 
       Sum(sfrentaldays + wfrentaldays) 
       AS RentalDays, 
       Sum(sfrentaldays * sfrentalrate) 
       AS SFBilling, 
       Sum(wfrentaldays * wfrentalrate) 
       AS WFBilling, 
       Sum(rigupcharge + rigdowncharge) 
       AS RigCharges, 
       Sum(jobrentalticket.toolredresscharge * jobrentalticket.numberoftools) 
       AS 
       SumOfToolRedressCharge, 
       Sum(sfinsurancedays * sfinsurancerate) 
       AS SFInsurance, 
       Sum(wfinsurancedays * wfinsurancerate) 
       AS WFInsurance, 
       Sum(servicemandays * servicemanrate) 
       AS ServiceCalls, 
       Sum(jobrentalticket.othercharges) 
       AS SumOfOthercharges, 
       Sum(sfstandbydays * sfstandbyrate) 
       AS SFStandby, 
       Sum(wfstandbydays * wfstandbyrate) 
       AS WFStandby, 
       Sum(creditdays * creditrate) 
       AS Credit, 
       Sum(( sfrentaldays * sfrentalrate ) + ( wfrentaldays * wfrentalrate ) + ( 
               rigupcharge + rigdowncharge ) + ( 
           jobrentalticket.toolredresscharge * 
           jobrentalticket.numberoftools ) + ( 
               sfinsurancedays * sfinsurancerate ) + ( 
           wfinsurancedays * wfinsurancerate ) 
           + ( servicemandays * servicemanrate ) + ( 
           jobrentalticket.othercharges ) + ( 
               sfstandbydays * sfstandbyrate ) + 
           ( wfstandbydays * wfstandbyrate ) - ( 
           creditdays * creditrate )) 
       AS Total, 
       Sum(( ( sfrentaldays * sfrentalrate ) + ( wfrentaldays * wfrentalrate ) + 
             ( 
                   rigupcharge + rigdowncharge ) + ( 
             jobrentalticket.toolredresscharge * 
             jobrentalticket.numberoftools ) + ( 
                   sfinsurancedays * sfinsurancerate ) + 
                   ( wfinsurancedays * wfinsurancerate ) + 
                   ( servicemandays * servicemanrate ) + 
             ( jobrentalticket.othercharges ) + 
                 ( 
                   sfstandbydays * sfstandbyrate ) + ( 
             wfstandbydays * wfstandbyrate ) 
             - ( 
                 creditdays * creditrate ) ) / (( sfrentaldays + wfrentaldays )) <---------------------------Division here!
       )AS 
       'Daily Average' 
FROM   job 
       INNER JOIN jobrentalticket 
               ON job.jobid = jobrentalticket.jobid 
WHERE  ( ( ( job.onlocationdatetime ) > '2014-01-01 12:00:00 AM' ) 
         AND ( ( job.onlocationdatetime ) < '2015-01-31 11:59:59 PM' ) ) 
GROUP  BY job.company 
HAVING (( ( job.company ) <> "test company" ))   

Open in new window

0
Comment
Question by:r3nder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40575629
I don't think we can help you, since:
We don't know the logic.
We don't know the values.
We don't know the returning result.
We don't know the expected result.
0
 
LVL 6

Author Comment

by:r3nder
ID: 40575645
I am asking you to look at this line and tell me if it is divided correctly? - you don't need logic just - is the division part correct
Sum(( ( sfrentaldays * sfrentalrate ) + ( wfrentaldays * wfrentalrate ) + 
             ( 
                   rigupcharge + rigdowncharge ) + ( 
             jobrentalticket.toolredresscharge * 
             jobrentalticket.numberoftools ) + ( 
                   sfinsurancedays * sfinsurancerate ) + 
                   ( wfinsurancedays * wfinsurancerate ) + 
                   ( servicemandays * servicemanrate ) + 
             ( jobrentalticket.othercharges ) + 
                 ( 
                   sfstandbydays * sfstandbyrate ) + ( 
             wfstandbydays * wfstandbyrate ) 
             - ( 
                 creditdays * creditrate ) ) / (( sfrentaldays + wfrentaldays )) <---------------------------Division here!
       )AS 
       'Daily Average' 

Open in new window

0
 
LVL 6

Accepted Solution

by:
r3nder earned 0 total points
ID: 40575667
Fixed - as usual people here are quick to make excuses when they dont know the problem was sum(value)/Sum(value) not Sum(value/value) as average
Sum(( ( sfrentaldays * sfrentalrate ) + ( wfrentaldays * wfrentalrate ) + 
             ( 
                   rigupcharge + rigdowncharge ) + ( 
             jobrentalticket.toolredresscharge * 
             jobrentalticket.numberoftools ) + ( 
                   sfinsurancedays * sfinsurancerate ) + 
                   ( wfinsurancedays * wfinsurancerate ) + 
                   ( servicemandays * servicemanrate ) + 
             ( jobrentalticket.othercharges ) + 
                 ( 
                   sfstandbydays * sfstandbyrate ) + ( 
             wfstandbydays * wfstandbyrate ) 
             - ( 
                 creditdays * creditrate ) )) /Sum( (( sfrentaldays + wfrentaldays )) <---------------------------Division here!
       )AS 
       'Daily Average' 

Open in new window

0
 
LVL 6

Author Comment

by:r3nder
ID: 40575804
I've requested that this question be closed as follows:

Accepted answer: 0 points for r3nder's comment #a40575667

for the following reason:

No one offered a solution - again
0
 
LVL 18

Expert Comment

by:Simon
ID: 40575688
1. if the datatype of the first expression (creditdays * creditrate) is an integer you may be getting integer dvision instead of decimal division

2. I think your expression is too long to easily understand without example data. Averaging aggregated values doesn't produce the same value as averaging the pre-aggregated values (i.e. it's not a weighted average).

A bigger brain may be able to help you, but for me, I'd find it much easier with some example data and/or your actual and expected results.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question