CODEWITHSUNDEEP
mysql
barracuda
barracuda

Reputation: 1048

MYSQL Calculate average of a specific occurance in a column

I need to calculate the average of occurrences in a dataset for a given value in a column. I made an easy example but in my current database contains around 2 inner joins to reduce it to 100k records. I need to perform the following select distinct statement for 10 columns.

My current design forces an inner join for each column. Another constraint is that I need to perform it at least 50-100 rows for each name in this example.

I need to figure out an efficient way to calculate this values without using too many resources while making the query fast.

http://sqlfiddle.com/#!9/c2378/3

My expected Result is:

Name    |  R Avg dir    |  L Avg dir 1   |  L Avg dir 2   |  L Avg dir 3
A          0               .5                 .25            .25

Create table query:

 CREATE TABLE demo
    (`id` int, `name` varchar(10),`hand` varchar(1), `dir` int)
;

INSERT INTO demo
    (`id`, `name`, `hand`, `dir`)
VALUES
    (1, 'A', 'L', 1),
    (2, 'A', 'L', 1),
    (3, 'A', 'L', 2),
    (4, 'A', 'L', 3),
    (5, 'A', 'R', 3),
    (6, 'A', 'R', 3)
;

Example Query:

SELECT distinct name, 
 COALESCE(( (Select count(id) as 'cd' from demo where hand = 'L' AND dir = 1) /(Select count(id) as 'fd' from demo where hand = 'L')),0) as 'L AVG dir'
FROM
  demo
  where hand = 'L' AND dir = 1 AND name = 'A'

Upvotes: 1

Views: 212

Answers (1)

sgeddes
sgeddes

Reputation: 62841

One option is to use conditional aggregation:

SELECT name, 
       count(case when hand = 'L' and dir = 1 then 1 end) / 
            count(case when hand = 'L' then 1 end) L1Avg,
       count(case when hand = 'L' and dir = 2 then 1 end) /
            count(case when hand = 'L' then 1 end) L2Avg,
       count(case when hand = 'L' and dir = 3 then 1 end) /
            count(case when hand = 'L' then 1 end) L3Avg,
       count(case when hand = 'R' and dir = 3 then 1 end) /
            count(case when hand = 'R' then 1 end) RAvg
FROM demo
WHERE name = 'A'
GROUP BY name

Please note, I wasn't 100% sure why you wanted your RAvg to be 0 -- I assumed you meant 100%. If not, you can adjust the above accordingly.

Upvotes: 1

Related Questions