test/sql/Aggregate/exp.test036.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 echo SELECT * from t1;
   9 ---------------------------------------------------------
  10         t1.f1   t1.f2   t1.f3   t1.f4
  11 ---------------------------------------------------------
  12         1       70      Nihar Pital     NULL
  13         2       70      Bijay Sahoo     MGR
  14         3       60      Kisor Ambli     Developer
  15         2       90      Jiten Lenka     S/W
  16         3       80      Papu Paital     NULL
  17         4       90      Bhahmananda     MKTING
  18
  19 Statement Executed
  20 Statement Executed: Rows Affected = 1
  21 Statement Executed: Rows Affected = 1
  22 Statement Executed: Rows Affected = 1
  23 Statement Executed: Rows Affected = 1
  24 Statement Executed: Rows Affected = 1
  25 Statement Executed: Rows Affected = 1
  26 Statement Executed: Rows Affected = 1
  27 echo SELECT * from t1;
  28 ---------------------------------------------------------
  29         t1.f1   t1.f2   t1.f3   t1.f4
  30 ---------------------------------------------------------
  31         1       70      Nihar Pital     NULL
  32         2       70      Bijay Sahoo     MGR
  33         3       60      Kisor Ambli     Developer
  34         2       90      Jiten Lenka     S/W
  35         3       80      Papu Paital     NULL
  36         4       90      Bhahmananda     MKTING
  37
  38 echo select t1.f1,t1.f2,count(t1.f4),count(t2.f3) from t1,t2 group by t1.f1,t1.f2 ;
  39 ---------------------------------------------------------
  40         t1.f1   t1.f2   COUNT(t1.f4)    COUNT(t2.f3)
  41 ---------------------------------------------------------
  42         1       70      0       7
  43         2       70      7       7
  44         3       60      7       7
  45         2       90      7       7
  46         3       80      0       7
  47         4       90      7       7
  48
  49 echo select t1.f1,t1.f2,count(t1.f4),count(t2.f3) from t1,t2 group by t1.f1,t1.f2 having count(t1.f4)<6 and count(t2.f3) >=7;
  50 ---------------------------------------------------------
  51         t1.f1   t1.f2   COUNT(t1.f4)    COUNT(t2.f3)
  52 ---------------------------------------------------------
  53         1       70      0       7
  54         3       80      0       7
  55
  56 echo select t1.f1,t1.f2,count(t1.f3),count(t2.f3) from t1,t2 group by t1.f1,t1.f2 having avg(t1.f2)<70;
  57 ---------------------------------------------------------
  58         t1.f1   t1.f2   COUNT(t1.f3)    COUNT(t2.f3)
  59 ---------------------------------------------------------
  60         3       60      7       7
  61
  62 Statement Executed
  63 Statement Executed