CODEWITHSUNDEEP
mysqlsql
user3233787
user3233787

Reputation: 377

SQL Query Sum condition

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions