Welcome Guest, Not a member yet? Register   Sign In
SQL Query with SUM()
#1

(This post was last modified: 03-03-2022, 09:35 AM by BFlokstra. Edit Reason: Formatting )

Hi guys,
Here is some background:
I am building a claim system for our volunteers. Any time a volunteer uses their own car to get to a shift, they get €0,19 per kilometer to go towards gas. If they use public transport they get 100% of the fare reimbursed. 
What I'm trying to do now is get all claims for a specific volunteer, with a total of the payable price. Now, the volunteers can claim multiple shifts in one claim. To this end, I have set up two tables:

claims:
id int [PRIMARY, auto_inc]
volunteer int,
date date
approved date
payed date
denied date
iban varchar[18]
account_holder TEXT


claimrows:
id int [PRIMARY, auto_inc]
claim int
shift int
address TEXT
kilometers int
amount double(5,2)
vehicle_type int
payable double(5,2)
(To make understanding the below easier, I translated the table, column and row names to english. I haven't changed anything else, so it's exactly how it's set up)
I've written this query:

Code:
SELECT
claims.id,
    claims.volunteer,
    claims.date,
    claims.approved,
    claims.payed,
    claims.denied,
    claims.iban,
    claims.account_holder,
    (
        SELECT DISTINCT
        SUM(payable)
        FROM
        claimsrows
        WHERE
        claimsrows.claim = claims.id
    ) AS totaal
FROM
`claims`
INNER JOIN
`claimsrows` ON claims.id = claimsrows.claim
WHERE
claimsrows.volunteer = 1

I'm now having two problems:

  1. I get one result for every claimrow for this volunteer (with id 1). That volunteer has one claim, with 3 claimrows. How can I set this query up so I get only one result when there is only one claim, two results when there are two claims, etc, independent of the number of claimrows per claim?
  2. I have no idea how to get this query set up with the query builder. I have never done any subqueries with it. Some pointers would be much appreciated!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB