Sum of a column is not giving the expected value

Asked

Viewed 181 times

-2

I have a fictitious table with two columns and some data in it, according to the image.

inserir a descrição da imagem aqui

When I run my SQL statement below, the result of the query is 22. Shouldn’t the result be only 12? I couldn’t understand why.

Could someone explain to me?

SQL:

SELECT sum(a.coluna1)
  FROM dados AS A 
 WHERE EXISTS (SELECT TRUE 
                 FROM dados AS B
                 WHERE (a.coluna1/b.coluna2) >= 1);
sql postgresql

  • Solved your question ?

  • Vote today! Vote tomorrow! Vote forever! Vote consciously! Your vote is very important to our community, contribute to us, and help make Stack Overflow in Portuguese (Sopt) bigger and bigger. You can learn more at: Vote early, vote often

3 answers

1

This your SQL does not make much sense, I believe that what you wanted to do was the following:

 SELECT sum(coluna1)
 FROM dados 
 WHERE ((coluna1/coluna2) >= 1)

See if it works that way.

What this SQL I sent you does, is add the values when the column 1 / column2 is greater than or equal to 1.

0

Analyzing your SQL:

SELECT sum(a.coluna1)
  FROM dados AS A 
 WHERE EXISTS (SELECT TRUE 
                 FROM dados AS B
                 WHERE (a.coluna1/b.coluna2) >= 1);

It returns 22 because you tell it to add up the column a.coluna1 when the condition in the clause WHERE amid "()" exist, she returned TRUE, so he added, if he returns FALSE he would add nothing.

I believe that’s what you wanted:

SELECT SUM(a.coluna1)
  FROM dados AS a
 WHERE id IN (SELECT id
                FROM dados AS b
               WHERE (a.coluna1 / b.coluna2) >= 1);

Thus, your query will add the column data a.coluna1 where the id exist within the condition passed in the clause WHERE amid "()".

0

The problem is that in subquery you say the current line of table A when divided by whichever line of table B, the result is maior/igual to 1, you will add the value of column 1.

Using the same syntax to solve this problem, simply specify that in subselect the line from which the column B shall be the same line as the column a, Take the example:

SELECT sum(a.coluna1)
  FROM dados AS A 
 WHERE EXISTS (SELECT TRUE 
                 FROM dados AS B
                 WHERE a.coluna1 = b.coluna1 and (a.coluna1/b.coluna2) >= 1);

However, this code doesn’t make much sense...

As Mr Isaias has shown, there is little point in giving a subselect with Exists to make that calculation, just do as he did.

Browser other questions tagged sql postgresql

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