MySQL - SELECT AVG on some rows and SUM on all

Question

I am trying to average values of some specific rows but sum volumes of all of them.

I have 2 tables, one called exchanges and another one called valid_country.

exchanges

+-----------+----------+--------------+----------+--------+
|     id    |   ref    |     country  |   value  | volume |
+-----------+----------+--------------+----------+--------+
|     1     |  1029    |      DE      |   1 000  |   100  |
+-----------+----------+--------------+----------+--------+
|     2     |  1029    |      US      |   2 000  |   250  |
+-----------+----------+--------------+----------+--------+
|     3     |  1029    |      FR      |   3 500  |   300  |
+-----------+----------+--------------+----------+--------+
|     4     |  1053    |      UK      |   1 200  |   110  |
+-----------+----------+--------------+----------+--------+
|     5     |  1029    |      RU      |     900  |    70  |
+-----------+----------+--------------+----------+--------+

This table contains many references (ref) which have different countries, themselves with different values and volumes.

valid_country

+--------------+--------------+
|     ref      |   country    |
+--------------+--------------+
|    1029      |     US       |
+--------------+--------------+
|    1029      |     RU       |
+--------------+--------------+
|    1053      |     UK       |
+--------------+--------------+

This table lists all the 'good' countries for which values can be averaged.

What I would like as a result query is :

+----------+------------+-------------+
|   ref    | AVG(value) | SUM(volume) |
+----------+------------+-------------+
|  1029    |    1 450   |     720     |
+----------+------------+-------------+
|  1053    |    1 200   |     110     |
+----------+------------+-------------+

Firstly ref are GROUP BY. Ref 1029 shall AVERAGE values of only US and RU (because of table valid_country) but SUM volumes of all countries. Same thing for Ref 1053 but since there's only one row it is easy.

Here is a little Fiddle. The SQL request is false since it averages all countries and not only the good one.

Answer 1

You can use a LEFT JOIN and the CASE statement to ignore some values in the AVG (SQLFiddle):

SELECT e.ref,
       AVG(CASE WHEN vc.country IS NOT NULL THEN e.value END) AS average,
       SUM(e.volume) AS volume
FROM exchanges e
LEFT JOIN valid_country vc ON ( vc.country = e.country )
GROUP BY e.ref

CASEreturns NULLif not matched, and AVG ignores those values:

|  ref | average | volume |
|------|---------|--------|
| 1029 |    1450 |    720 |
| 1053 |    1200 |    110 |

Answer 2

I think the comparison to valid_country needs to use both ref and country:

SELECT e.ref,
       AVG(CASE WHEN vc.country IS NOT NULL THEN e.value END) AS average,
       SUM(e.volume) AS volume
FROM exchanges e LEFT JOIN
     valid_country vc
     ON vc.country = e.country AND vc.ref = e.ref
GROUP BY e.ref;

This doesn't matter for your sample data but it might be important for the larger problem.

Answer 3

This query makes use of a subquery to deliver the correct results:

SELECT e1.ref,subq.avg,SUM(volume)
FROM exchanges e1,
 (SELECT e2.ref, AVG(value) as avg 
  FROM exchanges e2, valid_country vc
  WHERE e2.country = vc.country
  GROUP BY e2.ref) as subq
WHERE e1.ref = subq.ref
GROUP BY ref