This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

Untitled

By: a guest on Nov 14th, 2012  |  syntax: None  |  size: 0.90 KB  |  views: 25  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. level      box
  2. 1           1
  3. 2           2
  4. 3           2
  5. 1           3
  6. 2           1
  7. 2           3
  8. 2           3
  9. 3           1
  10.        
  11. box1     box2         box3
  12. level1    1        0            1
  13. level2    1        1            2
  14. level3    1        1            0
  15.        
  16. SELECT
  17.   CONCAT('level', level) AS levelname,
  18.   COUNT(CASE WHEN box = 1 THEN box ELSE NULL END) AS box1,
  19.   COUNT(CASE WHEN box = 2 THEN box ELSE NULL END) AS box2,
  20.   COUNT(CASE WHEN box = 3 THEN box ELSE NULL END) AS box3
  21. FROM tbl
  22. GROUP BY levelname
  23.        
  24. SELECT
  25.   CONCAT('level', a.level) AS levelname,
  26.   b1.num AS box1,
  27.   b2.num AS box2,
  28.   b3.num AS box3
  29. FROM
  30.   table a
  31.   LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 1) b1 ON a.level = b1.level
  32.   LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 2) b2 ON a.level = b2.level
  33.   LEFT JOIN (SELECT level, COUNT(*) AS num FROM table WHERE box = 3) b3 ON a.level = b3.level
clone this paste RAW Paste Data