Advertisement
Guest User

Untitled

a guest
Jul 26th, 2016
52
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.89 KB | None | 0 0
  1. DROP TABLE IF EXISTS t1;
  2. CREATE TABLE IF NOT EXISTS t1 (
  3. b BOOLEAN,
  4. i INTEGER,
  5. x INTEGER
  6. );
  7.  
  8. INSERT INTO t1 (i, x, b) VALUES (1 ,2, false), (2, 3, true), (3, 5, false), (1, 7, false), (2, 11, true), (3, 13, true), (1, 17, false), (2, 19, true), (3, 23, true), (1, 29, true), (2, 31, false), (3, 37, false);
  9.  
  10. SELECT i, sum(x) as sumX, count(b) as cntB FROM (
  11. SELECT * FROM t1) as tt
  12. GROUP BY i;
  13.  
  14. SELECT i FROM (
  15. SELECT i, sum(x) as sumX FROM t1 GROUP BY i) as tt
  16. ORDER BY sumX;
  17.  
  18. SELECT sqrt(i), sumX FROM (
  19. SELECT * FROM (
  20. SELECT i, sum(x) as sumX FROM t1 GROUP BY i) as tt
  21. ORDER BY sumX) as ttt
  22. ORDER BY sqrt(i);
  23.  
  24.  
  25. ## Failing
  26. SELECT i FROM (
  27. SELECT i, sum(x) as sumX, count(b) as cntB FROM t1 GROUP BY i) as tt
  28. WHERE sumX > 60;
  29.  
  30. SELECT i FROM (
  31. SELECT i, b FROM t1 GROUP BY i, b) as tt
  32. GROUP BY i;
  33.  
  34. SELECT i, avg(x) as avgX FROM (
  35. SELECT * from t1 WHERE b = true ORDER BY x) as tt
  36. GROUP BY i;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement