mysql query to find sum of different names

Question

I use similar queries (10) as following queries (modified) to find sum

SELECT sum(amount) AS amount 
  FROM `students` 
 WHERE sex='M' 
    && name in ('salil', 'anil', 'gaikwad')

...and:

SELECT sum(amount) AS amount 
  FROM `students` 
 WHERE sex='M' 
    && name in ('salil1', 'anil1', 'gaikwad1')

i want to make a single query of the above 10 queries. is it possible?

Answer 1

Try something like this

SELECT sum(amount) AS amount 
FROM    students  INNER JOIN
     (SELECT 'salil%' Val UNION SELECT 'anil%' UNION SELECT 'gaikwad%') s ON students.NAME LIKE s.Val
WHERE   sex='M'

This allows you to use the values in the second Table to join with LIKE.

Answer 2

You can use UNION

SELECT 'subset1', sum(amount) AS amount FROM students WHERE sex='M' and name in ('salil', 'anil', 'gaikwad')
UNION
SELECT 'subset2', sum(amount) AS amount FROM students WHERE sex='M' and name in ('salil1', 'anil1', 'gaikwad1')

However, you probably query these sets of students for a reason, perhaps anil, salil and gaikwad are one group of students. If so, you should reflect this in the database structure, not in your code.

You could add a field 'SUbset' or 'Group' or whatever that is, to students table, so it looks like this:

name group_id
salil 1
anil 1
gaikwad 1
salil1 2
...

Then you can do

select group_id, sum(amount) from students group by group_id