CODEWITHSUNDEEP
mysql
Fehu
Fehu

Reputation: 381

Sum the same value with different condition

Ok, the title is cryptic but I don't know how to sintetize it better.

I have a series of expensive similar SELECT SUM queries that must be executed in sequence.

Example:

SELECT SUM(t2.Field) 
FROM Table1 AS t1
INNER JOIN (
  SELECT Field FROM Table2
  WHERE [list of where]
) AS t2 ON ti.ExtKey = t2.Key
WHERE t1.TheValue = 'Orange'

SELECT SUM(t2.Field) 
FROM Table1 AS t1
INNER JOIN (
  SELECT Field FROM Table2
  WHERE [list of where]
) AS t2 ON ti.ExtKey = t2.Key
WHERE t1.TheValue = 'Apple'

And so on. I've used the nested inner join because after some test it resulted faster than a plain Join.

The rows selected for Table2 are always the same, or at least the same for session. There's a way to group all the queries in one to speed up the execution?

I was thinking about using a material view, but this would complicate very much the design and maintenance.

Upvotes: 1

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I think you want a group by:

SELECT t1.TheValue, SUM(t2.Field) 
FROM Table1 t1 INNER JOIN
     (SELECT Field
      FROM Table2
      WHERE [list of where]
     ) t2
     ON t1.ExtKey = t2.Key
GROUP BY t1.theValue;

Note that your query doesn't quite make sense, because t2 doesn't have a column called key. I assume this is an oversight in the question.

If you want to limit it to particular values, then use a WHERE clause before the GROUP BY:

WHERE t1.TheValue IN ('Apple', 'Orange', 'Pear')

Upvotes: 0

Alex
Alex

Reputation: 17289

I am no sure about your goal. I have a guess for you:

http://sqlfiddle.com/#!9/af66e/2

http://sqlfiddle.com/#!9/af66e/1

SELECT 
  SUM(IF(t1.TheValue = 'Orange',t2.Field,0)) as oranges,
  SUM(IF(t1.TheValue = 'Apple',t2.Field,0)) as apples
FROM Table1 AS t1
INNER JOIN (
  SELECT Field, `key` FROM Table2

) AS t2 ON t1.ExtKey = t2.`key`
# GROUP BY t1.extkey uncomment if you need it

If you can provide raw data sample and expected result that would help a lot.

Upvotes: 3

Related Questions