CODEWITHSUNDEEP
mysqlsqlcalculated-columnscalculation
Cosmin
Cosmin

Reputation: 585

Multiply several columns in 2 different tables by multiple rates (mysql)

I hope someone could help me figure this out quick. For some reason I just don't get how to multiply my table with some rates. Here's what I am facing:

Table1 - LocTbl

ID Location Inspections ServiceCalls RegularHrs OTHrs
1 LA         1             3            7         2
2 LA         2             4            6         3
3 LA         5             1            8         1

Table2 - Rates

Location RateType      Rate
LA       Inspections    100
LA       ServiceCalls   42
LA       RegularHrs     20
LA       OTHRs          30
SF       Inspections    110
SF       ServiceCalls   45

Desired result:

Location InspectionCost ServiceCallCost RegularHrsCost OTHrsCost TotalCost
LA       800            336             630            120       1886

What I need is every column total from the Location table multiplied by the corresponding rate and the sum of those columns. Something like this:

SELECT 
Sum(Location.Inspections)*Rates.Rate as InspectionCost, 
Sum(Location.ServiceCalls)*Rates.Rate as ServiceCallCost, 
Sum(Location.RegularHrs)*Rates.Rate as RegularHrsCost, 
Sum(Location.OTHRs)*Rates.Rate as OTHrsCost
[-- >Sum(InspectionCost)+Sum(ServiceCallCost)+Sum(RegularHRsCost)+Sum(OTHrsCost) as TotalCost  <--] 
^ how can I calculate the totals for the columns already multiplied?
FROM Location, Rates
WHERE Rates.Rate = ? {how do I reference all of the corresponding rates?}

Can someone please help me with some straight forward suggestions of how can I reach the result that I am looking for. Any workaround is welcomed.

Upvotes: 0

Views: 342

Answers (1)

etsa
etsa

Reputation: 5060

You can try this. I wrote it whitout testing (I have to go home now). For any thing wrong I can help you tomorrow.

SELECT A.LOCATION
        , A.T_Inspections*B1.Rate as InspectionCost
        , A.T_ServiceCall*B2.Rate as ServiceCallCost
        , A.T_RegularHrs*B3.Rate as RegularHrsCost
        , A.T_OTHrs*B4.Rate as OTHrsCost
        , A.T_Inspections*B1.Rate + A.T_ServiceCall*B2.Rate+A.T_RegularHrs*B3.Rate+A.T_OTHrs*B4.Rate AS TOTAL_COST
FROM (SELECT  LOCATION, 
        Sum(Inspections) AS T_Inspections, 
        Sum(ServiceCalls)AS T_ServiceCall, 
        Sum(RegularHrs)AS T_RegularHrs, 
        Sum(OTHRs) AS T_OTHrs   
        FROM Location 
        GROUP BY LOCATION) A
INNER JOIN RATES B1 ON A.LOCATION = B1.LOCATION AND B1.RATETYPE='Inspections' 
INNER JOIN RATES B2 ON A.LOCATION = B2.LOCATION AND B2.RATETYPE='ServiceCalls' 
INNER JOIN RATES B3 ON A.LOCATION = B3.LOCATION AND B3.RATETYPE='RegularHrs' 
INNER JOIN RATES B4 ON A.LOCATION = B4.LOCATION AND B4.RATETYPE='OTHRs'

Upvotes: 1

Related Questions