calculate averages for students and levels, MySQL

calculate averages for students and levels

DROP TABLE IF EXISTS PROGalphabet; 
CREATE TABLE PROGalphabet (
  rid int PRIMARY KEY auto_increment,
  sid int NOT NULL,
  date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  level enum('Alphabet in Order','Point ABCs randomly','Tell ABCs randomly','knows phonics','starts reading') NOT NULL,
  Percent tinyint NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO PROGalphabet (rid, sid, date, level, Percent) VALUES
(36, 45, '2019-01-20 15:51:42', 'Alphabet in Order', 61),
(37, 45, '2019-01-20 15:52:00', 'Alphabet in Order', 77),
(38, 45, '2019-01-20 15:57:11', 'Alphabet in Order', 85),
(51, 45, '2019-01-21 13:27:10', 'Alphabet in Order', 80),
(52, 45, '2019-01-21 13:37:27', 'Alphabet in Order', 67),
(54, 45, '2019-01-23 01:30:41', 'Alphabet in Order', 37),
(77, 45, '2019-01-29 02:10:57', 'Point ABCs randomly', 37),
(78, 45, '2019-01-29 02:10:59', 'Point ABCs randomly', 100),
(79, 45, '2018-12-12 06:46:22', 'starts reading', 1),
(80, 45, '2019-01-30 06:46:25', 'starts reading', 26),
(91, 45, '2019-04-11 16:00:00', 'starts reading', 60),
-- extra data for demonstration:
(39, 5, '2019-01-20 15:51:42', 'Tell ABCs randomly', 61),
(40, 5, '2019-01-20 15:52:00', 'Alphabet in Order', 77),
(41, 5, '2019-01-20 15:57:11', 'Alphabet in Order', 85),
(42, 5, '2019-01-21 13:27:10', 'Alphabet in Order', 80),
(43, 5, '2019-01-21 13:37:27', 'Alphabet in Order', 67),
(44, 5, '2019-01-23 01:30:41', 'Alphabet in Order', 57),
(45, 5, '2019-01-29 02:10:57', 'Point ABCs randomly', 67),
(46, 5, '2019-01-29 02:10:59', 'Point ABCs randomly', 80),
(47, 5, '2018-12-12 06:46:22', 'Knows phonics', 75),
(48, 5, '2019-01-30 06:46:25', 'starts reading', 26),
(49, 5, '2019-04-11 16:00:00', 'starts reading', 60);
select sid,level,percent from PROGalphabet;

SELECT sid,COUNT(ttl) level_count, SUM(ttl) perc_sum, SUM(ttl)/5 average_required, AVG(ttl) average_so_far FROM (
 SELECT sid,level, AVG(percent) ttl FROM PROGalphabet GROUP BY sid,level
) t GROUP BY sid


run \| edit \| history \| help	0