MySQL query - Getting turnover for all trucks belonging to company

    Good day all,


    I have two tables. The first table has a list of vehicles, with the companies they are assigned to. Each company has multiple trucks.


    Users then input the turnovers generated by each truck each day in table 2 by way of a userform that references all the trucks from a list pulled from table one. This data is then submitted back to the MYSQL database. All programming is done in VBA. This is what the tables looks like:


    TABLE1 - List of trucks and companies (First row is column names):


    TRUCKREGCOMPANY
    Reg123CompA
    Reg456CompB
    Reg789CompC
    Reg012CompC
    Reg023CompA


    TABLE2 - List of trucks and turnovers (First row is column names):


    TRUCKTURNOVER
    Reg1231500
    Reg7891200
    Reg1231300
    Reg0121600
    Reg4561750
    Reg0231850
    Reg4561100
    Reg7891350
    Reg023650


    I need a MYSQL query that sums all the turnover generated by a specific company's trucks. It therefore needs to look at TABLE1, see which trucks belong to CompA, and return a total turnover for all its trucks for a specified date range.


    Different recordsets are to be hardcoded per company, hence I only need the correct MYSQL query for the first company. Replicating it for the other companies will be easy. In my actual database there are hundreds of transactions spread over each calendar year.


    Apologies for not posting any code. I have no problem accessing my database, pulling/submitting data from it etc. Also, I've searched high and low on various other forums, but I could not find a problem posted elsewhere that was similar to this (or I completely missed it). I know this should not be difficult, but for some reason I can't wrap my head around the correct query.


    Any help with the MYSQL query would be appreciated!


    Regards

    dwsteyl

    Here is a SQL statement I created using the data in an Access DB You should be able to adapt to MYSQL


    SQL
    SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER
FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK
GROUP BY Table1.COMPANY, Table2.TRUCK;


    Putting in VBA format


    Code
    strSql = "SELECT Table1.COMPANY, Table2.TRUCK, Sum(Table2.TURNOVER) AS SumOfTURNOVER " & vbCrLf & _
"FROM Table1 INNER JOIN Table2 ON Table1.TRUCKREG = Table2.TRUCK " & vbCrLf & _
"GROUP BY Table1.COMPANY, Table2.TRUCK;"

    Alansidman,


    Thank you for sending me in the right direction. I need to add a few extra conditions in my WHERE statement, but your SQL statement helped solved where I went wrong.


    Thanks again!

    wesselsteyl

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login