1 echo CREATE TABLE t1
(f1 int
,f2 int
,f3 char
(15));
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 ---------------------------------------------------------
28 ---------------------------------------------------------
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
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
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
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