GROUP BY In SQL Server

Using Group By, you have sum, min, max, count based on value

Below query return sum of all quantity based on CustomerID

select CustomerID ,SUM(Qty) from ProductDetailTable group by CustomerID

 

IF you want to take greater then 100 Qty based on CustmerID then user Having Clause

select CustomerID ,SUM(Qty) from ProductDetailTable group by CustomerID  having SUM(Qty)>100

 

Having Clasue used with Group By Clasuse

hi,

Grouping records is the process of combining like SELECT columns together. Once combined, Aggregate functions such as SUM and AVERAGE can be performed on the set.
GROUP BY should be used to apply aggregate operators to each group.

SELECT Employee, Rank
FROM Employees
GROUP BY Employee, Rank

http://msdn.microsoft.com/en-us/library/ms177673.aspx

hope this will help you.

Following example will show why we use "GROUP BY"

SQL GROUP BY Example

We have the following "Orders" table:
O_IdOrderDateOrderPriceCustomer

1	2008/11/12	1000	Hansen
2	2008/10/23	1600	Nilsen
3	2008/09/02	700	Hansen
4	2008/09/03	300	Hansen
5	2008/08/30	2000	Jensen
6	2008/10/04	100	Nilsen

Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer

The result-set will look like this:
CustomerSUM(OrderPrice)

Hansen	2000
Nilsen	1700
Jensen	2000

Nice! Isn't it? :)
Let's see what happens if we omit the GROUP BY statement:
SELECT Customer,SUM(OrderPrice) FROM Orders

The result-set will look like this:
CustomerSUM(OrderPrice)

Hansen	5700
Nilsen	5700
Hansen	5700
Hansen	5700
Jensen	5700
Nilsen	5700

The result-set above is not what we wanted.


If it helps you, pls make it accepted.
Thanks