Subtract a sum value (SUM) from SQL?

Asked

Viewed 697 times

0

Good afternoon! I have an SQL query, which searches and sums up the amount of credits a given user has:

SELECT *, SUM(quantidade) as total_creditos FROM creditos INNER JOIN usuarios ON creditos.id_usuario = usuarios.id WHERE creditos.id_usuario = '$id_usuario'`

In the credit table, I have the following structure:

Tabela creditos

A user can have multiple Inserts in the table, with X credits.

The question is: can I make a query and decrease (delete) a Y value of a user’s total SUM of credits?

Thanks in advance!

php sql

  • What is the condition of the value to be replaced? A certain credit id, user...?

  • User ID. It is at the end of the query. It would be the consumption of a given item... any value, but would have to change in the table!

  • One way you can replace it is not to include it in SUM(), that is, filter this value in the query by adding this condition in WHERE along with the user ID that is already there, or using the IF function inside the SUM to replace it with zero. Another possibility is to subtract this value with a SUM(quantity) - (SELECT...) subquery. What was not clear to me is the criterion that identifies the "given item" so that a solution can be created with it.

  • So Gustavo, in my case, the user will enjoy his available credit (determined by himself the amount). But as @Emerson Ferreira Bezerra explained to me, the bank was unviable from my way of thinking. I ended up doing as he suggested: create the debit field and do the calculation to know the current amount of user credits: SELECT *, (SUM(credito) - SUM(debito)) as total_creditos FROM creditos INNER JOIN usuarios ON creditos.id_usuario = usuarios.id WHERE creditos.id_usuario = '$id_usuario'

1 answer

1


As you are dynamically making a total sum of the amounts of credit, there is no way to decrease some value of this total directly on a permanent basis. You will need to somehow record this amount to be discounted.

I think of two different paths that can be followed:

  • Create a new table where users' debits would be posted and in your query add this table and subtract these debit values from the credits to know the user(s) balance(s)

  • Using the same credit table, making entries with negative credit value for the user(s) in question. In this way the changes would be minimized.

The first option seems to be more elegant and robust, the second most practical.

  • I will test here, and give an opinion later! Thank you!

  • thanks! I used the practical form! =)

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.