test/sql/Order/exp.test006.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 Statement Executed: Rows Affected = 1
  12 Statement Executed: Rows Affected = 1
  13 echo SELECT * FROM t1;
  14 ---------------------------------------------------------
  15         t1.f1   t1.f2   t1.f3   t1.f4
  16 ---------------------------------------------------------
  17         3       AAA     2.500000        2008/7/1
  18         2       AAA     2.500000        2008/7/2
  19         4       GGG     4.500000        2008/7/3
  20         1       DEF     1.500000        2008/7/1
  21         6       CCC     6.500000        2008/7/2
  22         5       HHH     5.500000        2008/7/3
  23         2       AAA     2.500000        2008/7/1
  24         6       CCC     6.500000        2008/7/4
  25         3       ABB     2.600000        2008/7/5
  26         2       BBB     1.600000        2008/7/4
  27         1       DDD     1.600000        2008/7/1
  28
  29 echo SELECT f1, f2, SUM(f3) FROM t1 GROUP BY f1, f2 ORDER BY f1;
  30 ---------------------------------------------------------
  31         f1      f2      SUM(f3)
  32 ---------------------------------------------------------
  33         1       DDD     1.600000
  34         1       DEF     1.500000
  35         2       BBB     1.600000
  36         2       AAA     5.000000
  37         3       ABB     2.600000
  38         3       AAA     2.500000
  39         4       GGG     4.500000
  40         5       HHH     5.500000
  41         6       CCC     13.000000
  42
  43 echo SELECT f1, f2, SUM(f3), COUNT(f4) FROM t1 GROUP BY f1, f2 ORDER BY f1, f2;
  44 ---------------------------------------------------------
  45         f1      f2      SUM(f3) COUNT(f4)
  46 ---------------------------------------------------------
  47         1       DDD     1.600000        1
  48         1       DEF     1.500000        1
  49         2       AAA     5.000000        2
  50         2       BBB     1.600000        1
  51         3       AAA     2.500000        1
  52         3       ABB     2.600000        1
  53         4       GGG     4.500000        1
  54         5       HHH     5.500000        1
  55         6       CCC     13.000000       2
  56
  57 echo SELECT f1, SUM(f3), MIN(f2), MAX(f4) FROM t1 GROUP BY f1 ORDER BY f1;
  58 ---------------------------------------------------------
  59         f1      SUM(f3) MIN(f2) MAX(f4)
  60 ---------------------------------------------------------
  61         1       3.100000        DDD     2008/7/1
  62         2       6.600000        AAA     2008/7/4
  63         3       5.100000        AAA     2008/7/5
  64         4       4.500000        GGG     2008/7/3
  65         5       5.500000        HHH     2008/7/3
  66         6       13.000000       CCC     2008/7/4
  67
  68 echo SELECT f1, SUM(f3), MIN(f2), MAX(f4) FROM t1 GROUP BY f1 HAVING SUM(f3) <= 5.5 ORDER BY f1;
  69 ---------------------------------------------------------
  70         f1      SUM(f3) MIN(f2) MAX(f4)
  71 ---------------------------------------------------------
  72         1       3.100000        DDD     2008/7/1
  73         3       5.100000        AAA     2008/7/5
  74         4       4.500000        GGG     2008/7/3
  75         5       5.500000        HHH     2008/7/3
  76
  77 Statement Executed