Group By and having clause
consider the below table: product
|
Pno |
Qty |
|
P1 |
10 |
|
P2 |
12 |
|
P3 |
10 |
|
P1 |
20 |
|
P4 |
19 |
|
P1 |
9 |
|
P2 |
8 |
|
P3 |
10 |
|
P4 |
2 |
|
P1 |
5 |
Select pno from product group by pno;
P1
P2
P3
P4
Select pno,sum(qty) from product group by pno;
P1 44
P2 20
P3 20
P4 21
Select pno,count(pno) from product group by pno;
P1 4
P2 2
P3 2
P4 2
All group function can be used (sum ,count,avg, max,.min)
Select pno,count(pno),sum(qty),max(qty),min(qty),avg(qty) from product group by pno;
|
Pno |
Count(pno) |
Sum(qty) |
Max(qty) |
Min(qty) |
Avg(qty) |
|
P1 |
4 |
44 |
20 |
5 |
11 |
|
P2 |
2 |
20 |
12 |
8 |
10 |
|
P3 |
2 |
20 |
10 |
10 |
10 |
|
P4 |
2 |
21 |
19 |
2 |
11.5 |
Having clause
Having clause helps us to apply condition along with group by clause.
Select pno,sum(qty) from product group by pno having pno=’p1’ or pno=’p2’;
P1 44
P2 20
