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