How to SUM the value of the specific rows only?

user_12192308 0
Viewed 231

Would like to get the SUM of two specific rows in a table in mysql.

create table people(
    id int,
    firstname varchar(50),
    money int
);

id   firstname    money
1    david        100
2    jack         200
3    bob          300

Say I want the SUM of row 1(david) and row 3(bob) only. The total should be 400.

I used this query:

SELECT SUM(money) 
FROM people 
WHERE id = 1 AND 3;

but it turned out to be 100, and not 400 as I expected.

mysql sql
5 Answers

Here's your query. you can use case and in to get this.

select sum(case when id = 1 or id = 3 then money else 0 end) from people

or 

select sum(money) from people where id in (1, 3)
user_64290720

The answer given by @Metal is the way to go:

SELECT SUM(money)
FROM people
WHERE id IN (1, 3);

I am mainly posting this to explain why your current query is giving you a sum of 100. The reason is that the WHERE condition:

WHERE id = 1 AND 3

will only ever be true when id = 1, i.e. for David's record. The value 3 is evaluating to true always. So, you simply sum David's record and get a total of 100.

user_18632290

You have to use OR to select both the rows.

SELECT SUM(money) 
FROM people 
WHERE id = 1 OR id = 3;
user_67035690

you can find like this:

SELECT SUM(money)
FROM people
WHERE id =1
union
SELECT SUM(money)
FROM people
WHERE id =3
user_13328700

You Have to use this way.

    SELECT SUM(money) as Money
FROM people
WHERE id Between 1 and 3
user_83370850
Related