test/sql/Aggregate/exp.testnw011.ksh

   1 Network CSql
   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 ---------------------------------------------------------
  13         t1.f1   t1.f2   t1.f3
  14 ---------------------------------------------------------
  15         1       10      100
  16         2       20      200
  17         3       30      300
  18         NULL    20      500
  19         12      20      500
  20         1       NULL    400
  21         1       11      500
  22         13      30      200
  23         14      31      300
  24
  25 Network CSql
  26 echo select f3, avg(f1) from t1 group by f3;
  27 ---------------------------------------------------------
  28         f3      AVG(f1)
  29 ---------------------------------------------------------
  30         100     1.000000
  31         200     7.500000
  32         300     8.500000
  33         500     6.500000
  34         400     1.000000
  35
  36 echo select f3, min(f2) from t1 group by f3;
  37 ---------------------------------------------------------
  38         f3      MIN(f2)
  39 ---------------------------------------------------------
  40         100     10
  41         200     20
  42         300     30
  43         500     11
  44         400     NULL
  45
  46 echo select f3, count(f1) from t1 group by f3;
  47 ---------------------------------------------------------
  48         f3      COUNT(f1)
  49 ---------------------------------------------------------
  50         100     1
  51         200     2
  52         300     2
  53         500     2
  54         400     1
  55
  56 echo select f3, sum(f2) from t1 group by f3;
  57 ---------------------------------------------------------
  58         f3      SUM(f2)
  59 ---------------------------------------------------------
  60         100     10
  61         200     50
  62         300     61
  63         500     51
  64         400     NULL
  65
  66 echo select f3, max(f1) from t1 group by f3;
  67 ---------------------------------------------------------
  68         f3      MAX(f1)
  69 ---------------------------------------------------------
  70         100     1
  71         200     13
  72         300     14
  73         500     12
  74         400     1
  75
  76 echo select f1, avg(f2) from t1 group by f1;
  77 ---------------------------------------------------------
  78         f1      AVG(f2)
  79 ---------------------------------------------------------
  80         1       10.500000
  81         2       20.000000
  82         3       30.000000
  83         NULL    20.000000
  84         12      20.000000
  85         13      30.000000
  86         14      31.000000
  87
  88 echo select f1, count(f3) from t1 group by f1;
  89 ---------------------------------------------------------
  90         f1      COUNT(f3)
  91 ---------------------------------------------------------
  92         1       3
  93         2       1
  94         3       1
  95         NULL    1
  96         12      1
  97         13      1
  98         14      1
  99
 100 echo select f1, min(f2) from t1 group by f1;
 101 ---------------------------------------------------------
 102         f1      MIN(f2)
 103 ---------------------------------------------------------
 104         1       10
 105         2       20
 106         3       30
 107         NULL    20
 108         12      20
 109         13      30
 110         14      31
 111
 112 echo select f1, sum(f3) from t1 group by f1;
 113 ---------------------------------------------------------
 114         f1      SUM(f3)
 115 ---------------------------------------------------------
 116         1       1000
 117         2       200
 118         3       300
 119         NULL    500
 120         12      500
 121         13      200
 122         14      300
 123
 124 echo select f1, max(f1) from t1 group by f1;
 125 ---------------------------------------------------------
 126         f1      MAX(f1)
 127 ---------------------------------------------------------
 128         1       1
 129         2       2
 130         3       3
 131         NULL    NULL
 132         12      12
 133         13      13
 134         14      14
 135
 136 echo select f2, avg(f2) from t1 group by f2;
 137 ---------------------------------------------------------
 138         f2      AVG(f2)
 139 ---------------------------------------------------------
 140         10      10.000000
 141         20      20.000000
 142         30      30.000000
 143         NULL    NULL
 144         11      11.000000
 145         31      31.000000
 146
 147 echo select f2, count(f2) from t1 group by f2;
 148 ---------------------------------------------------------
 149         f2      COUNT(f2)
 150 ---------------------------------------------------------
 151         10      1
 152         20      3
 153         30      2
 154         NULL    0
 155         11      1
 156         31      1
 157
 158 echo select f2, min(f2) from t1 group by f2;
 159 ---------------------------------------------------------
 160         f2      MIN(f2)
 161 ---------------------------------------------------------
 162         10      10
 163         20      20
 164         30      30
 165         NULL    NULL
 166         11      11
 167         31      31
 168
 169 echo select f2, sum(f2) from t1 group by f2;
 170 ---------------------------------------------------------
 171         f2      SUM(f2)
 172 ---------------------------------------------------------
 173         10      10
 174         20      60
 175         30      60
 176         NULL    NULL
 177         11      11
 178         31      31
 179
 180 echo select f2, max(f2) from t1 group by f2;
 181 ---------------------------------------------------------
 182         f2      MAX(f2)
 183 ---------------------------------------------------------
 184         10      10
 185         20      20
 186         30      30
 187         NULL    NULL
 188         11      11
 189         31      31
 190
 191 Statement Executed