Question 20678 in Mysql Question posted in
The official MySQL documentation can be found here.

SQL query to find sum of two particular rows – Mysql

This is my table :

id      name         age          role
---------------------------------------
1       Rahul            20              1
2       Karan            35              0
3       Vivek            47              0
4       Shubham          40              1
5       Paras            87              1
6       Arjun            18              1
7       Ayush            28              0

I want to select only those two rows which are having the sum of their ages equal to 60 like we have
1st and 4th row in above table. What is the SQL query for that??

I have tried this

select * from form_data where age=(select age from form_data having sum(age)=60);
Tags:

3

Answers


  1. 0

    One way to do it is to treat the same table as a second table and join them

    select fd1.name as name1, fd2.name as name2, fd1.age as age1, fd2.age as age2
from form_data as fd1, form_data as fd2 
where fd1.age+fd2.age = 60

    This will give the two names and the two ages that match. However this will also give you Rahul+Shubam and Shubam+Rahul.

    One way of getting around that is to use the id

    select fd1.name as name1, fd2.name as name2, fd1.age as age1, fd2.age as age2
from form_data as fd1, form_data as fd2 
where fd1.age+fd2.age = 60 and fd2.id > fd1.id
    Login or Signup to reply.
  2. 0

    This is a use case for a CROSS JOIN to get every combination of rows from 2 tables, using the form_data table twice:

    SELECT *
FROM form_data a
    CROSS JOIN form_data b
WHERE a.age + b.age = 60
    -- the following condition prevents getting a combination twice
    AND a.id < b.id
    Login or Signup to reply.
  3. 0

    Using that table:

    CREATE TABLE IF NOT EXISTS users(
`id` int,
`name` VARCHAR(10),
`age` INT,
`role` INT
);

INSERT INTO `testing_database`.`users` (`id`,`name`,`age`,`role`)
VALUES
(1,       "Rahul",          20,              1),
(2,       "Karan",          35,              0),
(3,       "Vivek",          47,              0),
(4,       "Subam",          40,              1),
(5,       "Paras",          87,              1),
(6,       "Arjun",          18,              1),
(7,       "Ayush",          28,              0)

    I’ve returned the data with thit query:

    SELECT t1.id, 
       t1.name, 
       t1.role, 
       t1.age as t1_age, 
       t2.age as t2_age,
       (t1.age  + t2.age ) as sum_age
FROM testing_database.users as t1
CROSS JOIN testing_database.users as t2
ON t1.id != t2.id
WHERE (t1.age  + t2.age) = 60
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search