test/sql/Aggregate/exp.testnw036.ksh

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