CODEWITHSUNDEEP
mysqlsql
priest
priest

Reputation: 31

How to SUM the value of the specific rows only?

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.

Upvotes: 3

Views: 835

Answers (5)

Md.Rezwan
Md.Rezwan

Reputation: 51

You Have to use this way.

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

Upvotes: 0

CompEng
CompEng

Reputation: 7376

you can find like this:

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

Upvotes: 0

Bhawan
Bhawan

Reputation: 2491

You have to use OR to select both the rows.

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

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.

Upvotes: 8

Ed Bangga
Ed Bangga

Reputation: 13006

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)

Upvotes: 8

Related Questions