CODEWITHSUNDEEP
mysqlsumaggregateaverage
Alex Man
Alex Man

Reputation: 4886

Sum and Average Percentage Calculation using MYSql

I have a Statistics table as give below

parent     key      value   date
MASTER     maths    400     2016-10-12
MASTER     maths    200     2016-10-20
MASTER     maths    350     2016-10-23
MASTER     passed   20%     2016-10-25
MASTER     passed   40%     2016-10-27
MASTER     passed   30%     2016-10-28
MASTER     maths    500     2016-11-12
MASTER     maths    250     2016-11-20 
MASTER     maths    600     2016-11-23 
MASTER     passed   55%     2016-11-25
MASTER     passed   75%     2016-11-27
MASTER     maths    500     2016-12-12
MASTER     maths    250     2016-12-20 
MASTER     maths    600     2016-12-23 
MASTER     passed   70%     2016-12-25
MASTER     passed   65%     2016-12-26
MASTER     passed   45%     2016-12-27
MASTER     maths    500     2017-01-12
MASTER     maths    450     2017-01-20 
MASTER     maths    770     2017-01-23 
MASTER     passed   87%     2017-01-25
MASTER     passed   54%     2017-01-27
MASTER     maths    900     2017-02-12
MASTER     maths    250     2017-02-20 
MASTER     maths    500     2017-02-23 
MASTER     passed   67%     2017-02-25
MASTER     passed   45%     2017-02-27

I got a requirement to bring up an output like as shown below using mysql 

maths  passed   date
950    30%      2016-10
1350   65%      2016-11
1350   60%      2016-12
1720   70.5%    2017-01
1650   56%      2017-02

My Query which I have wrote is given below

SELECT SUM(CASE WHEN key = 'maths' THEN value ELSE 0 END) AS maths,
       SUM(CASE WHEN key = 'passed' THEN value ELSE 0 END) AS passed 
FROM Statistics
WHERE parent = 'MASTER' GROUP BY MONTH(date) ORDER BY date ASC

But I am the sum of passed instead of the average, that is lets say if I get 20%, 40% and 30% then I should get the average as 30% but for my above query I am getting 90%. Also the % is also disappeared in the result

Can anyone please help me on this

Upvotes: 0

Views: 768

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

You have to use AVG aggregate function for average.

Change: 

SUM(CASE WHEN key = 'passed' THEN value ELSE 0 END) AS passed

TO:

AVG(CASE WHEN key = 'passed' THEN value ELSE 0 END) AS passed

% symbols is removed

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

To get the % symbol displayed, you can contact it with results.
Following change in your query, may help you getting the desired results.

mysql> SELECT DATE_FORMAT( `date`, '%Y-%m' ) AS `month`
    ->      , SUM( CASE `key` WHEN 'maths' THEN `value` ELSE NULL END ) AS `maths`
    ->      -- , SUM( CASE `key` WHEN 'passed' THEN `value` ELSE NULL END ) AS `passed`
    ->      , CONCAT( ROUND( AVG( CASE `key` WHEN 'passed' THEN `value` ELSE NULL END ), 2 ), '%' ) AS `passedAvg`
    ->   FROM Statistics
    ->  WHERE parent = 'MASTER'
    ->  GROUP BY MONTH(`date`)
    ->  ORDER BY `date` ASC;

Results can be like this: 

+---------+-------+-----------+
| month   | maths | passedAvg |
+---------+-------+-----------+
| 2016-10 |   950 | 30.00%    |
| 2016-11 |  1350 | 65.00%    |
| 2016-12 |  1350 | 60.00%    |
| 2017-01 |  1720 | 70.50%    |
| 2017-02 |  1650 | 56.00%    |
+---------+-------+-----------+

Documentation Reference:

  • Type Conversion in Expression Evaluation
    • When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa

Upvotes: 1

McNets
McNets

Reputation: 10827

Use a subquery:

To calculate AVG, first need to know how many rows has 'passed' values.

(Check it here.)

select yr as `year`, mt as `month`,
       sum(val_maths) as `math`,
       concat(cast((sum(val_passed) / sum(count_passed)) as char(50)), '%')  as `passed`
from (
       select ky, year(dt) as yr, month(dt) as mt,
              if (ky = 'maths', replace(val,'%',''), 0) val_maths,
              if (ky = 'passed', replace(val,'%',''), 0) val_passed,
              if (ky = 'passed', 1, 0) as count_passed    
       from foot
     ) tv
group  by yr, mt
;

| year  | month | maths  | passed |
|-------|-------|--------|--------|
| 2016  | 10    | 950    | 30%    |
| 2016  | 11    | 1350   | 65%    |
| 2016  | 12    | 1350   | 60%    |
| 2017  | 1     | 1720   | 70.5%  |
| 2017  | 2     | 1650   | 56%    |

Upvotes: 1

Related Questions