test/sql/Aggregate/exp.testnw013.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 having sum(f2) >300 ;
  43 ---------------------------------------------------------
  44         f1      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2) COUNT(f3)
  45 ---------------------------------------------------------
  46         2       301     4       75.250000       70      91      4
  47
  48 echo SELECT f3,count(f1),sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f3 having avg(f2) <75;
  49 ---------------------------------------------------------
  50         f3      COUNT(f1)       SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  51 ---------------------------------------------------------
  52         Nihar Pital     2       141     2       70.500000       70      71
  53         Bijay Sahoo     4       292     4       73.000000       70      80
  54         Kisor Ambli     1       60      1       60.000000       60      60
  55         Papu Paital     2       141     2       70.500000       60      81
  56
  57 echo SELECT f1,sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f1;
  58 ---------------------------------------------------------
  59         f1      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  60 ---------------------------------------------------------
  61         1       293     4       73.250000       70      80
  62         2       301     4       75.250000       70      91
  63         3       201     3       67.000000       60      81
  64
  65 echo SELECT f1,sum(f2),count(f2),avg(f2),min(f2),max(f2) from t1 GROUP BY f1 having avg(f2) <70 or min(f2)=70;
  66 ---------------------------------------------------------
  67         f1      SUM(f2) COUNT(f2)       AVG(f2) MIN(f2) MAX(f2)
  68 ---------------------------------------------------------
  69         1       293     4       73.250000       70      80
  70         2       301     4       75.250000       70      91
  71         3       201     3       67.000000       60      81
  72
  73 Statement Executed