r3nder
asked on
Division in MySQL
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?
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" ))
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.
ASKER
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Accepted answer: 0 points for r3nder's comment #a40575667
for the following reason:
No one offered a solution - again
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.
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.