AKSA-EFI/SprintAuswertung

Wiki-Quelltext dieser Seite.

<language>SQL</language>
= Übung: Auswertungen von Arbeit für verschiedene Projekte mit SQL GROUP BY, SUM, AVG, ... = 

<code id="4d0f5784b858a" id="" hidden>
DROP TABLE IF EXISTS work_report;
CREATE TABLE work_report (
  id INT NOT NULL,
  sprint INT NOT NULL,
  status VARCHAR(20),
  resolution VARCHAR(20),
  project VARCHAR(20),
  story_points INT,
  work_done DOUBLE,
  planned BOOLEAN
);

INSERT INTO work_report VALUES (3656, 1, 'closed', 'fixed', 'Admin', 0, 64.1, 1);
INSERT INTO work_report VALUES (3638, 1, 'closed', 'fixed', 'Admin', 0, 1, 1);
INSERT INTO work_report VALUES (3636, 1, 'closed', , 'Admin', 0, 39, 1);
INSERT INTO work_report VALUES (3644, 1, 'closed', 'fixed', 'Admin', 0, 8.75, 1);
INSERT INTO work_report VALUES (3652, 1, 'closed', 'fixed', 'Admin', 0, 59, 1);
INSERT INTO work_report VALUES (3650, 1, 'closed', 'fixed', 'Slytherin', 0, 16, 1);
INSERT INTO work_report VALUES (3648, 1, 'closed', 'fixed', 'Hufflepuff', 0, 12.4, 1);
INSERT INTO work_report VALUES (3646, 1, 'closed', 'fixed', 'Gryffindor', 0, 3, 1);
INSERT INTO work_report VALUES (3647, 1, 'closed', 'fixed', 'Gryffindor', 0, 23.5, 1);
INSERT INTO work_report VALUES (3651, 1, 'closed', 'fixed', 'Operation', 0, 4.75, 1);
INSERT INTO work_report VALUES (3640, 1, 'closed', 'fixed', 'Operation', 0, 71.75, 1);
INSERT INTO work_report VALUES (3649, 1, 'closed', 'fixed', 'Ravenclaw', 0, 11, 1);
INSERT INTO work_report VALUES (3808, 2, 'closed', 'fixed', 'Gryffindor', 0, 12.3, 1);
INSERT INTO work_report VALUES (3807, 2, 'closed', 'fixed', 'Admin', 0, 75.5, 1);
INSERT INTO work_report VALUES (3798, 2, 'closed', 'fixed', 'Admin', 0, 14.3, 1);
INSERT INTO work_report VALUES (3784, 2, 'closed', 'fixed', 'Admin', 0, 57.4, 1);
INSERT INTO work_report VALUES (3924, 2, 'closed', 'fixed', 'Gryffindor', 0, 4, 1);
INSERT INTO work_report VALUES (3794, 2, 'closed', 'fixed', 'Operation', 0, 25.5, 1);
INSERT INTO work_report VALUES (3809, 2, 'closed', 'wontfix', 'Hufflepuff', 0, 0, 1);
INSERT INTO work_report VALUES (3792, 2, 'closed', 'fixed', 'Admin', 0, 3.4, 1);
INSERT INTO work_report VALUES (3804, 2, 'closed', 'fixed', 'Admin', 0, 1.5, 1);
INSERT INTO work_report VALUES (3767, 2, 'closed', 'fixed', 'Admin', 0, 22, 1);
INSERT INTO work_report VALUES (3803, 2, 'closed', , 'Slytherin', 0, 10.8, 1);
INSERT INTO work_report VALUES (3801, 2, 'closed', 'fixed', 'Hufflepuff', 0, 10, 1);
INSERT INTO work_report VALUES (3790, 2, 'closed', 'fixed', 'Admin', 0, 30.5, 1);
INSERT INTO work_report VALUES (3800, 2, 'closed', 'fixed', 'Gryffindor', 0, 14, 1);
INSERT INTO work_report VALUES (3802, 2, 'closed', 'fixed', 'Ravenclaw', 0, 9, 1);
INSERT INTO work_report VALUES (3955, 3, 'new', , 'Admin', 0, 13.5, 1);
INSERT INTO work_report VALUES (3961, 3, 'closed', 'fixed', 'Admin', 0, 62.2, 1);
INSERT INTO work_report VALUES (3947, 3, 'closed', , 'Admin', 0, 37.5, 1);
INSERT INTO work_report VALUES (4098, 3, 'closed', 'fixed', 'Hufflepuff', 0, 5, 1);
INSERT INTO work_report VALUES (3957, 3, 'closed', 'fixed', 'Hufflepuff', 0, 6, 1);
INSERT INTO work_report VALUES (4092, 3, 'closed', , 'Operation', 0, 2, 1);
INSERT INTO work_report VALUES (3978, 3, 'new', , 'Admin', 0, 60.5, 1);
INSERT INTO work_report VALUES (3959, 3, 'new', , 'Ravenclaw', 0, 6, 1);
INSERT INTO work_report VALUES (3953, 3, 'new', , 'Admin', 0, 3.6, 1);
INSERT INTO work_report VALUES (4069, 3, 'new', , 'Gryffindor', 0, 8.5, 1);
INSERT INTO work_report VALUES (3949, 3, 'accepted', , 'Operation', 0, 61.5, 1);
INSERT INTO work_report VALUES (3628, 1, 'closed', 'fixed', 'Gryffindor', 1, 36, 1);
INSERT INTO work_report VALUES (3589, 1, 'closed', 'fixed', 'Gryffindor', 8, 162.9, 1);
INSERT INTO work_report VALUES (3660, 3, 'closed', 'fixed', 'Slytherin', 8, 158.4, 1);
INSERT INTO work_report VALUES (3591, 2, 'closed', 'fixed', 'Hogsmeade', 1, 19, 1);
INSERT INTO work_report VALUES (3899, 3, 'closed', 'fixed', 'ShriekingShack', 1, 9.5, 0);
INSERT INTO work_report VALUES (3590, 2, 'closed', 'fixed', 'Gryffindor', 5, 86, 1);
INSERT INTO work_report VALUES (3621, 3, 'closed', 'fixed', 'Hufflepuff', 1, 17, 1);
INSERT INTO work_report VALUES (3623, 2, 'closed', 'fixed', 'Gryffindor', 5, 82.5, 1);
INSERT INTO work_report VALUES (528, 1, 'closed', 'fixed', 'Improvements', 3, 44, 1);
INSERT INTO work_report VALUES (3936, 3, 'closed', 'fixed', 'Gryffindor', 5, 62.5, 0);
INSERT INTO work_report VALUES (3601, 3, 'closed', 'fixed', 'Ravenclaw', 3, 36, 1);
INSERT INTO work_report VALUES (3578, 3, 'closed', 'fixed', 'Ravenclaw', 1, 11.5, 1);
INSERT INTO work_report VALUES (3613, 3, 'closed', 'fixed', 'Hufflepuff', 3, 32, 1);
INSERT INTO work_report VALUES (3565, 2, 'closed', 'fixed', 'Operation', 2, 19.5, 0);
INSERT INTO work_report VALUES (3627, 1, 'closed', 'fixed', 'Gryffindor', 1, 9.25, 1);
INSERT INTO work_report VALUES (3931, 2, 'closed', 'fixed', 'Improvements', 1, 9, 1);
INSERT INTO work_report VALUES (3632, 2, 'closed', 'fixed', 'Gryffindor', 13, 111.5, 1);
INSERT INTO work_report VALUES (3945, 3, 'closed', , 'Improvements', 1, 8.5, 1);
INSERT INTO work_report VALUES (3631, 1, 'closed', 'fixed', 'Gryffindor', 5, 42, 1);
INSERT INTO work_report VALUES (3946, 3, 'closed', 'fixed', 'Gryffindor', 2, 15.5, 1);
INSERT INTO work_report VALUES (3607, 3, 'closed', 'fixed', 'Gryffindor', 3, 23, 1);
INSERT INTO work_report VALUES (3606, 3, 'closed', 'fixed', 'Gryffindor', 8, 61, 1);
INSERT INTO work_report VALUES (3908, 3, 'closed', 'fixed', 'Hufflepuff', 2, 15, 1);
INSERT INTO work_report VALUES (3605, 3, 'closed', 'fixed', 'Gryffindor', 5, 37, 1);
INSERT INTO work_report VALUES (3773, 3, 'closed', 'fixed', 'Slytherin', 2, 14.5, 1);
INSERT INTO work_report VALUES (3568, 3, 'closed', 'fixed', 'Operation', 5, 36, 0);
INSERT INTO work_report VALUES (3609, 3, 'closed', 'fixed', 'Hufflepuff', 8, 57.5, 1);
INSERT INTO work_report VALUES (3635, 1, 'closed', 'fixed', 'Operation', 1, 7, 1);
INSERT INTO work_report VALUES (3917, 3, 'closed', 'fixed', 'Hufflepuff', 1, 7, 1);
INSERT INTO work_report VALUES (4072, 3, 'new', , 'Gryffindor', 0, 32, 1);
INSERT INTO work_report VALUES (3776, 2, 'closed', 'fixed', 'Gryffindor', 3, 19, 1);
INSERT INTO work_report VALUES (4062, 3, 'new', , 'Ravenclaw', 0, 8, 1);
INSERT INTO work_report VALUES (4061, 3, 'new', , 'Slytherin', 0, 6.5, 1);
INSERT INTO work_report VALUES (4090, 3, 'new', , 'Slytherin', 0, 5.75, 1);
INSERT INTO work_report VALUES (4076, 3, 'closed', , 'Hogsmeade', 0, 1.5, 1);
INSERT INTO work_report VALUES (3581, 2, 'closed', 'fixed', 'Ravenclaw', 5, 30.5, 1);
INSERT INTO work_report VALUES (3909, 3, 'closed', 'fixed', 'Hufflepuff', 3, 18.25, 1);
INSERT INTO work_report VALUES (3543, 1, 'closed', 'fixed', 'Operation', 3, 18, 0);
INSERT INTO work_report VALUES (3898, 3, 'closed', 'fixed', 'ChangeRequest', 1, 6, 0);
INSERT INTO work_report VALUES (3905, 3, 'closed', 'fixed', 'Gryffindor', 1, 6, 0);
INSERT INTO work_report VALUES (3633, 2, 'closed', 'fixed', 'Gryffindor', 13, 77.2, 1);
INSERT INTO work_report VALUES (3612, 2, 'closed', 'fixed', 'Hufflepuff', 8, 47, 1);
INSERT INTO work_report VALUES (3967, 3, 'closed', 'fixed', 'ShriekingShack', 2, 11.5, 1);
INSERT INTO work_report VALUES (3906, 3, 'closed', 'fixed', 'Gryffindor', 3, 16, 0);
INSERT INTO work_report VALUES (3770, 3, 'closed', 'fixed', 'Slytherin', 5, 23, 1);
INSERT INTO work_report VALUES (3777, 1, 'closed', 'fixed', 'Hufflepuff', 5, 22.5, 1);
INSERT INTO work_report VALUES (3428, 1, 'closed', 'fixed', 'Improvements', 13, 57.5, 1);
INSERT INTO work_report VALUES (3604, 2, 'closed', 'fixed', 'Gryffindor', 3, 13.1, 1);
INSERT INTO work_report VALUES (3547, 1, 'closed', 'fixed', 'Operation', 8, 34.5, 0);
INSERT INTO work_report VALUES (3293, 3, 'closed', , 'Improvements', 2, 8.25, 1);
INSERT INTO work_report VALUES (3937, 3, 'closed', 'fixed', 'Slytherin', 5, 16.5, 1);
INSERT INTO work_report VALUES (3966, 3, 'closed', 'fixed', 'ShriekingShack', 2, 6, 1);
INSERT INTO work_report VALUES (3554, 2, 'closed', 'fixed', 'Operation', 8, 23, 1);
INSERT INTO work_report VALUES (3625, 3, 'closed', 'fixed', 'Gryffindor', 3, 6.5, 1);
INSERT INTO work_report VALUES (3037, 2, 'closed', 'fixed', 'Slytherin', 13, 14.05, 1);
INSERT INTO work_report VALUES (3941, 4, 'closed', 'fixed', 'Ravenclaw', 2, , );
INSERT INTO work_report VALUES (4257, 4, 'closed', 'fixed', 'Gryffindor', 0, , );
INSERT INTO work_report VALUES (4117, 4, 'new', , 'Ravenclaw', 0);
INSERT INTO work_report VALUES (4131, 4, 'new', , 'Admin', 0);
INSERT INTO work_report VALUES (3990, 4, 'closed', 'fixed', 'ShriekingShack', 2);
INSERT INTO work_report VALUES (3942, 4, 'accepted', '', 'Ravenclaw', 2);
INSERT INTO work_report VALUES (4115, 4, 'accepted', '', 'Slytherin', 0);
INSERT INTO work_report VALUES (4119, 4, 'new', '', 'Operation', 0);
INSERT INTO work_report VALUES (4127, 4, 'accepted', '', 'Operation', 0);
INSERT INTO work_report VALUES (4145, 4, 'closed', 'fixed', 'Hufflepuff', 3);
INSERT INTO work_report VALUES (4103, 4, 'closed', '', 'Slytherin', 1);
INSERT INTO work_report VALUES (4101, 4, 'closed', 'fixed', 'Operation', 1);
INSERT INTO work_report VALUES (4148, 4, 'accepted', '', 'Hufflepuff', 0);
INSERT INTO work_report VALUES (4144, 4, 'closed', 'fixed', 'Gryffindor', 8);
INSERT INTO work_report VALUES (4249, 4, 'new', '', 'Admin', 0);
INSERT INTO work_report VALUES (4114, 4, 'new', '', 'Gryffindor', 0);
INSERT INTO work_report VALUES (4110, 4, 'closed', 'fixed', 'Admin', 0);
INSERT INTO work_report VALUES (4109, 4, 'accepted', '', 'Operation', 8);
INSERT INTO work_report VALUES (4171, 4, 'new', '', 'Improvements', 5);
INSERT INTO work_report VALUES (4123, 4, 'new', '', 'Admin', 0);
INSERT INTO work_report VALUES (4134, 4, 'new', '', 'Ravenclaw', 0);
INSERT INTO work_report VALUES (4104, 4, 'new', '', 'Admin', 0);
INSERT INTO work_report VALUES (4116, 4, 'accepted', '', 'Hufflepuff', 0);
INSERT INTO work_report VALUES (3534, 4, 'closed', 'fixed', 'Hogsmeade', 2);
INSERT INTO work_report VALUES (4107, 4, 'closed', 'fixed', 'Operation', 1);
INSERT INTO work_report VALUES (3805, 4, 'closed', 'fixed', 'Admin', 0);
INSERT INTO work_report VALUES (4118, 4, 'accepted', '', 'ShriekingShack', 0);
INSERT INTO work_report VALUES (4120, 4, 'new', '', 'ChangeRequest', 0);
INSERT INTO work_report VALUES (4248, 4, 'new', '', 'Operation', 3);
INSERT INTO work_report VALUES (3662, 4, 'accepted', '', 'Slytherin', 2);
INSERT INTO work_report VALUES (4136, 4, 'new', '', 'Gryffindor', 0);
INSERT INTO work_report VALUES (4125, 4, 'new', '', 'Admin', 0);
INSERT INTO work_report VALUES (4168, 4, 'new', '', 'Improvements', 2);
INSERT INTO work_report VALUES (4149, 4, 'new', '', 'Feasibility', 0);
INSERT INTO work_report VALUES (4167, 4, 'new', '', 'Improvements', 5);
INSERT INTO work_report VALUES (4143, 4, 'accepted', '', 'Slytherin', 5);

</code>

== Alle Einträge der Tabelle work_report anzeigen ==
<eval id="4d0f5784b85f6" id="">
SELECT * FROM work_report;
</eval>