Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- level box
- 1 1
- 2 2
- 3 2
- 1 3
- 2 1
- 2 3
- 2 3
- 3 1
- box1 box2 box3
- level1 1 0 1
- level2 1 1 2
- level3 1 1 0
- SELECT
- CONCAT('level', level) AS levelname,
- COUNT(CASE WHEN box = 1 THEN box ELSE NULL END) AS box1,
- COUNT(CASE WHEN box = 2 THEN box ELSE NULL END) AS box2,
- COUNT(CASE WHEN box = 3 THEN box ELSE NULL END) AS box3
- FROM tbl
- GROUP BY levelname
- SELECT
- CONCAT('level', a.level) AS levelname,
- b1.num AS box1,
- b2.num AS box2,
- b3.num AS box3
- FROM
- table a
- LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 1) b1 ON a.level = b1.level
- LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 2) b2 ON a.level = b2.level
- LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 3) b3 ON a.level = b3.level
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement