test/sql/Aggregate/exp.test011.ksh

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