MySQL SubQueries for multiple SUMs

If I need to get five+ different SUM values with similar WHERE conditions, is the best (maybe only) way to do it through one query with five subqueries? For example, my query is:

SELECT user, 
(SELECT SUM(amount) FROM table t, table2 t2 WHERE t.table2_id = t2.id AND table2.type = 1 AND user = 1 AND date = x) AS one,
(SELECT SUM(amount) FROM table t, table2 t2 WHERE t.table2_id = t2.id AND table2.type = 2 AND user = 1 AND date = x) AS two, 
(SELECT SUM(amount) FROM table t, table2 t2 WHERE t.table2_id = t2.id AND table2.type = 3 AND user = 1 AND date = x) AS three, 
(SELECT SUM(amount) FROM table t, table2 t2 WHERE t.table2_id = t2.id AND table2.type = 4 AND user = 1 AND date = x) AS four, 
(SELECT SUM(amount) FROM table t, table2 t2 WHERE t.table2_id = t2.id AND table2.type = 5 AND user = 1 AND date = x) AS five 
FROM table 
WHERE user = 1

So far my queries have been very quick but I'm worried about the implication of five+ sub-queries when there's many more records.

And out of curiosity does one query like this with five sub-queries have a performance advantage / dis-advantage over five individual queries? I was assuming it's fa开发者_如何学JAVAster because it's only one trip to the database vs 5?

EDIT: There is a table 2 of about 60 records that are used to classify the thousands of records in table 1. Each record in table 2 is categorized by a type value of 1 - 5 (which is what I'm using to group items I want the SUM of).

1. Use ANSI JOINS
2. You are better writing your query in PIVOT form like the query below

Rewritten:

SELECT t.user, 
SUM(CASE table2.type WHEN 1 then amount else 0 end) As one,
SUM(CASE table2.type WHEN 2 then amount else 0 end) As two,
SUM(CASE table2.type WHEN 3 then amount else 0 end) As three,
SUM(CASE table2.type WHEN 4 then amount else 0 end) As four,
SUM(CASE table2.type WHEN 5 then amount else 0 end) As five
FROM table t
INNER JOIN table2 t2 ON t.table2_id = t2.id 
WHERE t.user = 1
  AND table2.type in (1,2,3,4,5)

I understand your query is pseudo but it has a hanging WHERE user without a FROM clause. As for performance, the above goes through the table in one pass. The 5x subquery form would have taken 5 passes, but should not be significantly slower if proper indexes are available.

You only need one query to get the sum for each type:

SELECT 
  t2.type, 
  SUM(t.amount) 
FROM 
  table t 
INNER JOIN 
  table2 t2 
ON 
  t.table2_id = t2.id 
AND 
  t.user = 1
AND 
  t.date = x 
GROUP BY 
  t2.type

well. if this just for one user, and you don't absolutely need it in that order....you could just do something like this..

select user, type, sum(amount)
where user = 1
and date = x
and type between (1,5)
group by user, type

this will give you

1,1,sum
1,2,sum
1,3,sum
1,4,sum
1,5,sum

instead of

1,sum1,sum2,sum3,sum4,sum5

This should be good enough for what you need I suspect without all that hassle.