SQL statement to calculate fine value

Question

SQL statement to calculate fine value

Navigation

#1 by (1 votes)

0

I have a system to calculate a payment of fines to a user, this has the following two tables: FINES and FINES_USERS

NOTES:

// In the table fines I have inserted the two types of fines, both normal and special, just change their type. (explained later)

// It is only possible to insert a type of fine for users, either normal or special.

TABLE OF FINES:

fine_type: one-digit integer, 1 = normal fine, 2 = special fine

num_multa: different numbers of fines for each corresponding fine

value: value of the fine

description_mult: describe the fine

Example:

fine_type: 1 num_mult: 1 value: 500 description_mult: walk out of grated

fine_type: 1 num_mult: 2 value: 1500 fine_ description: cross in red

fine_type: 1 num_mult: 3 value: 1300 description_mult: I disrespect the transit officer

fine_type: 2 num_mult: 1 value: 1000 description_mult: run through the grated

fine_type: 2 num_mult: 2 value: 2000 description_mult: crash and run away

fine_type: 2 num_mult: 3 value: 1700 description_mult: hit the transit officer.

TABLE OF FINES_USERS:

user_id: user identification number

fine_type: foreign key of the type of fine

multa_num: number of the fine

Example

user_id: 25171777 fine_type: 1 multa_num: 1

user_id: 25171777 fine_type: 1 multa_num: 2

userid: 12412421 fine_type: 2 multa_num: 1

userid: 12412421 fine_type: 2 multa_num: 2

I would like to be able to get the total value of the fine knowing that for each user you must add the values that correspond to each fine that has saved in FINES_USERS .

Taking an example:

User: 25171777:

type of fine: 1, number: 1 (brought from MULTAS_USUARIOS ), value: 500 ( brought from FINES )

type of fine: 1, number: 2 (brought from MULTAS_USUARIOS ), value: 1500 ( brought from FINES )

Total fines = 2000

That's why I need the SQL statement to get those values and add them, and also the formula that should be used

MANY THANKS

mysql sql

asked by Victor Alvarado 09.02.2017 в 20:13

source

1 answer

Django.como subtract amount entered minus stock in product table? SQLSTATE [23000]: Integrity constraint violation: 1452 Can not add or update a child row: a foreign key constraint fails

score 1 · Accepted Answer

The code is quite simple, you just have to do a JOIN between the tables and then use SUM :

SELECT  mu.id_usuario,
        SUM(m.valor) AS Total_Multas
FROM MULTAS_USUARIOS AS mu
INNER JOIN MULTAS AS m
    ON mu.multa_num = m.multa_num
WHERE mu.id_usuario = 25171777 
GROUP BY mu.id_usuario
;

Anyway, your design of the tables has a problem, you should not have the column multa_tipo in the table MULTAS_USUARIOS . This is because it is an "anti-pattern" design. Just have the number of the fine in the table MULTAS_USUARIOS , and the type you will get from the table MULTAS . In your current design, there is nothing to prevent at the database level that a fine number appears with a type in the MULTAS_USUARIOS table and a different type in the MULTAS table