SQL
SQL Server GroupBy and Having clause
Group by classifies identical data into groups. It is used with aggregate functions and comes after where clause and before order by clause
The following sales table is used to explain the concept:
| CustID | SellerID | Total |
|---|---|---|
| 1 | Null | 5 |
| 1 | Null | 10 |
| 1 | Null | 15 |
| 2 | Null | 20 |
| 3 | 6 | 25 |
| 3 | 6 | 30 |
| 3 | 5 | 60 |
| 3 | 5 | 65 |
| 4 | 6 | 90 |
| 4 | 5 | 100 |
| 4 | 5 | 105 |
Group by:
SELECT CustID, SellerID, Sum(Total) Total
FROM Sales
GROUP BY CustID, SellerID
ORDER BY CustID,SellerID
| CustID | SellerID | tot |
|---|---|---|
| 1 | Null | 30 |
| 2 | Null | 20 |
| 3 | 5 | 125 |
| 3 | 6 | 55 |
| 4 | 5 | 205 |
| 4 | 6 | 90 |
Having Clause:
SELECT CustID, SellerID, Sum(Total) Total
FROM Sales
GROUP BY CustID, SellerID
Having Sum(Total)>60
ORDER BY CustID,SellerID
| CustID | SellerID | Total |
|---|---|---|
| 3 | 5 | 125 |
| 4 | 5 | 205 |
| 4 | 6 | 90 |