test/sql/Aggregate/exp.test012.ksh

   1 echo CREATE TABLE t1(f1 int,f2 int,f3 char(15));
   2 Statement Executed
   3 echo INSERT INTO t1 VALUES(1,70,'Nihar Pital');
   4 Statement Executed: Rows Affected = 1
   5 echo INSERT INTO t1 VALUES(2,70,'Bijay Sahoo');
   6 Statement Executed: Rows Affected = 1
   7 echo INSERT INTO t1 VALUES(3,60,'Kisor Ambli');
   8 Statement Executed: Rows Affected = 1
   9 echo INSERT INTO t1 VALUES(2,90,'Jiten Lenka');
  10 Statement Executed: Rows Affected = 1
  11 echo INSERT INTO t1 VALUES(3,80,'Papu Paital');
  12 Statement Executed: Rows Affected = 1
  13 echo INSERT INTO t1 VALUES(1,80,'Bijay Sahoo');
  14 Statement Executed: Rows Affected = 1
  15 echo INSERT INTO t1 VALUES(2,70,'Jiten Lenka');
  16 Statement Executed: Rows Affected = 1
  17 echo INSERT INTO t1 VALUES(1,70,'Bijay Sahoo');
  18 Statement Executed: Rows Affected = 1
  19 echo INSERT INTO t1 VALUES(2,70,'Bijay Sahoo');
  20 Statement Executed: Rows Affected = 1
  21 echo INSERT INTO t1 VALUES(3,60,'Papu Paital');
  22 Statement Executed: Rows Affected = 1
  23 echo INSERT INTO t1 VALUES(1,70,'Nihar Pital');
  24 Statement Executed: Rows Affected = 1
  25 echo SELECT * FROM t1;
  26 ---------------------------------------------------------
  27         t1.f1   t1.f2   t1.f3
  28 ---------------------------------------------------------
  29         1       70      Nihar Pital
  30         2       70      Bijay Sahoo
  31         3       60      Kisor Ambli
  32         2       91      Jiten Lenka
  33         3       81      Papu Paital
  34         1       80      Bijay Sahoo
  35         2       70      Jiten Lenka
  36         1       72      Bijay Sahoo
  37         2       70      Bijay Sahoo
  38         3       60      Papu Paital
  39         1       71      Nihar Pital
  40
  41 echo SELECT f1,sum(f2),count(f2),avg(f2),min(f2),max(f2),count(f3) from t1 GROUP BY f1;
  42 ---------------------------------------------------------
  43         f1      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2) COUNT(f3)
  44 ---------------------------------------------------------
  45         1       293     4       73.250000       70      80      4
  46         2       301     4       75.250000       70      91      4
  47         3       201     3       67.000000       60      81      3
  48
  49 echo SELECT f3,count(f1),sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f3;
  50 ---------------------------------------------------------
  51         f3      COUNT(f1)       SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  52 ---------------------------------------------------------
  53         Nihar Pital     2       141     2       70.500000       70      71
  54         Bijay Sahoo     4       292     4       73.000000       70      80
  55         Kisor Ambli     1       60      1       60.000000       60      60
  56         Jiten Lenka     2       161     2       80.500000       70      91
  57         Papu Paital     2       141     2       70.500000       60      81
  58
  59 echo SELECT f1,sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f1,f3;
  60 ---------------------------------------------------------
  61         f1      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  62 ---------------------------------------------------------
  63         1       141     2       70.500000       70      71
  64         2       140     2       70.000000       70      70
  65         3       60      1       60.000000       60      60
  66         2       161     2       80.500000       70      91
  67         3       141     2       70.500000       60      81
  68         1       152     2       76.000000       72      80
  69
  70 echo SELECT f1,f3,sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f1,f3;
  71 ---------------------------------------------------------
  72         f1      f3      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  73 ---------------------------------------------------------
  74         1       Nihar Pital     141     2       70.500000       70      71
  75         2       Bijay Sahoo     140     2       70.000000       70      70
  76         3       Kisor Ambli     60      1       60.000000       60      60
  77         2       Jiten Lenka     161     2       80.500000       70      91
  78         3       Papu Paital     141     2       70.500000       60      81
  79         1       Bijay Sahoo     152     2       76.000000       72      80
  80
  81 Statement Executed