test/sql/Aggregate/exp.testnw014.ksh

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