Reputation: 377
I have three tables namely the class_record, class_violation, violation.
The table class_record has these columns and data:
Class Violation CR No. | Class ID
000-000 | A30-000
000-001 | A30-000
The table class_violation has these columns and data:
Class Violation CR No. | Violation ID
000-000 | 2
000-000 | 1
000-001 | 2
000-001 | 4
000-001 | 3
The table violation has these columns and data:
Violation ID | First Amount | Second Amount
1 | 1000 | 2000
2 | 200 | 400
3 | 500 | 1000
4 | 500 | 1000
The table class_record
contains the information of the class record.
The table class_violation
is the table that contains what are the violations that is committed. And lastly, the table violation
contains the information about the violations.
If the violation is committed twice, the second amount will be triggered instead of the first amount. As you can see on table class_violation
, on Violation ID
column, number 2 violation id is committed twice. The second amount of the must be the charged amount instead of the first amount. So the total charged amount will be the first amount plus the second amount if committed twice. My question is that how do I get the get the second amount instead of the first amount and get its total amount of the violations committed? So far here is my SQL query but is terribly wrong:
SELECT SUM(`First Amount`)
FROM violation
WHERE `Violation ID`
IN (SELECT `Violation ID` FROM class_violation
WHERE `Class Violation No.`
IN (SELECT `Class Violation CR No.`
FROM class_record WHERE `Class ID` = 'A30-000'))
Please help me. Sorry for my english. The result of the query must be:
SUM
2600
Here is my sqlfiddle link: http://sqlfiddle.com/#!2/2712a
Upvotes: 1
Views: 154
Reputation: 1271241
I would approach this by counting the violation ids for each whatever. You can do this in a subquery using variables:
select sum(case when rn = 1 then v.`First Amount`
when rn = 2 then v.`Second Amount`
end)
from (select cv.*,
@rn := if(@vi = `Violation ID`, @rn + 1, 1) as rn,
@vi := `Violation ID`
from class_violation cv cross join
(select @rn := 0, @vi := '') const
order by `Violation ID`
) cv join
violation v
on cv.`Violation ID` = v.`Violation ID` join
class_record cr
on cv.`Class Violation CR No.` = cr.`Class Violation CR No.`
where cr.`Class ID` = 'A30-000';
I would encourage you to rename the variables so you don't need the backticks. For instance, name First Amount
as FirstAmount
. It makes the queries easier to read and write.
Upvotes: 2