test/sql/Aggregate/exp.testnw012.ksh

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