CODEWITHSUNDEEP
mysqlsqlselect
Ed Pattermann
Ed Pattermann

Reputation: 33

MYSQL avoid SELECT computation if multiplying by ZERO

I am doing a SELECT from a MYSQL table

Table looks like this

year    tour      starts    scoring_avg
1990     EUR        5         71.56
1990     USA        6          0.0
1991     EUR        12        71.21
1991     USA        8         69.23

I am doing a SELECT like so

SELECT 
  year,
  SUM(starts),
  SUM(starts*scoring_avg) as scoring.avg
FROM scores
GROUP BY year

Goal is to get combined scoring average for the year, combining the EUR and USA rows for each year.

After the SELECT, I divide scoring_avg by starts. Since I added a 6 to starts from the second line, with no scoring_avg for that line, the result is not correct.

Works fine for year 1991. Doesn't work for year 1990, since the scoring_avg for the USA row is 0.

Is there a way the SELECT can be modified to only use the ps.starts*ps.scoring_avg in the SUM where ps.scoring_avg in that row is greater than 0?

Thank you.

-- Ed

Upvotes: 1

Views: 823

Answers (3)

spencer7593
spencer7593

Reputation: 108450

You could use an expression that conditionally returns either the value from the starts column, or 0 (or NULL), based on the value in the scoring_avg column. As an example:

IF(scoring_avg=0,0,starts)

That says, if scoring_avg has a value of zero (for a particular row), then return zero; otherwise, returns the value from the starts column. This is MySQL specific syntax, equivalent to the an ANSI-standard CASE expression:

CASE scoring_avg WHEN 0 THEN 0 ELSE starts END

In the context of your query:

SELECT s.year
     , SUM( IF(s.scoring_avg=0,0,s.starts) ) AS `starts`
     , SUM( s.starts*s.scoring_avg )         AS `scoring.avg`
     , SUM( s.starts*s.scoring_avg ) / 
       SUM( IF(s.scoring_avg=0,0,s.starts) ) AS `scoring.avg_div_starts`
  FROM scores s
 GROUP BY s.year

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If you just want to change scoringavg, use conditional aggregation:

SELECT year, SUM(starts),
       SUM(case when scoring_avg > 0 then starts*scoring_avg end) as scoring_avg
FROM scores
GROUP BY year;

However, I would suggest doing all the work in a single query and not doing any division afterwards. The following calculates the average that you want:

SELECT year, SUM(starts),
       (SUM(case when scoring_avg > 0 then starts*scoring_avg end) /
        SUM(scoring_avg > 0)
       ) as scoring_avg
FROM scores
GROUP BY year;

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172488

You may try this:

SELECT 
  year,
  SUM(starts),
  SUM(starts*scoring_avg) as scoring.avg
FROM scores
WHERE scoring_avg > 0
GROUP BY year

Upvotes: 1

Related Questions