test/sql/Aggregate/exp.test014.ksh

   1 echo create table t1(f1 char(10),f2 char(10),f3 int);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 Statement Executed: Rows Affected = 1
  12 Statement Executed: Rows Affected = 1
  13 Statement Executed: Rows Affected = 1
  14 Statement Executed: Rows Affected = 1
  15 echo select * from t1;
  16 ---------------------------------------------------------
  17         t1.f1   t1.f2   t1.f3
  18 ---------------------------------------------------------
  19         s       y       5000
  20         p       q       4000
  21         z       y       2000
  22         p       y       3000
  23         c       q       1000
  24         z       y       1500
  25         s       y       3000
  26         c       t       2500
  27         s       p       2300
  28         c       q       4000
  29         c       y       5000
  30         c       p       3500
  31
  32 echo select f2,avg(f3) from t1 group by f2;
  33 ---------------------------------------------------------
  34         f2      AVG(f3)
  35 ---------------------------------------------------------
  36         y       3250.000000
  37         q       3000.000000
  38         t       2500.000000
  39         p       2900.000000
  40
  41 echo select f2,avg(f3) from t1 where f3>=3000 group by f2 ;
  42 ---------------------------------------------------------
  43         f2      AVG(f3)
  44 ---------------------------------------------------------
  45         y       4000.000000
  46         q       4000.000000
  47         p       3500.000000
  48
  49 echo select f2,avg(f3) from t1 where f3>=2000 group by f2 having avg(f3)>3000;
  50 ---------------------------------------------------------
  51         f2      AVG(f3)
  52 ---------------------------------------------------------
  53         y       3600.000000
  54         q       4000.000000
  55
  56 echo select f2,avg(f3) from t1 where f3>=2000 group by f2 having avg(f3)>3000 limit 1;
  57 ---------------------------------------------------------
  58         f2      AVG(f3)
  59 ---------------------------------------------------------
  60         y       3600.000000
  61
  62 echo select f2,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f2 having avg(f3)>3000;
  63 ---------------------------------------------------------
  64         f2      AVG(f3)
  65 ---------------------------------------------------------
  66         y       3250.000000
  67         q       4000.000000
  68
  69 echo select f1,f2,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>3000 ;
  70 ---------------------------------------------------------
  71         f1      f2      AVG(f3)
  72 ---------------------------------------------------------
  73         s       y       4000.000000
  74         p       q       4000.000000
  75         c       q       4000.000000
  76         c       y       5000.000000
  77         c       p       3500.000000
  78
  79 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>3000;
  80 ---------------------------------------------------------
  81         f2      f1      AVG(f3)
  82 ---------------------------------------------------------
  83         y       s       4000.000000
  84         q       p       4000.000000
  85         q       c       4000.000000
  86         y       c       5000.000000
  87         p       c       3500.000000
  88
  89 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>2000 ;
  90 ---------------------------------------------------------
  91         f2      f1      AVG(f3)
  92 ---------------------------------------------------------
  93         y       s       4000.000000
  94         q       p       4000.000000
  95         y       p       3000.000000
  96         t       c       2500.000000
  97         p       s       2300.000000
  98         q       c       4000.000000
  99         y       c       5000.000000
 100         p       c       3500.000000
 101
 102 echo select f2,f1,avg(f3) from t1 where f3>=2000 or f2 = 'y' group by f1,f2 having avg(f3)>2000 limit 3 offset 2;
 103 ---------------------------------------------------------
 104         f2      f1      AVG(f3)
 105 ---------------------------------------------------------
 106         y       p       3000.000000
 107         t       c       2500.000000
 108         p       s       2300.000000
 109
 110 Statement Executed