Friday, April 18, 2014

T SQL Tutorial: GROUP BY


The TSQL GROUP BY clause is used to group data on the basis of columns. You can use group with or without aggregate function. All this be explained by example below.

The syntax for the SQL GROUP BY clause is:

SELECT expression1, expression2, ... expression_n,
       aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Using SUM function

Let say I want to get total sale amount of a customer. we will write the below query to sales amount for each customer

SELECT customerid, SUM(totalamount) AS "Total sales"
FROM order_details OD a inner join order o on od.order_id=o.order_id
GROUP BY customerid;

Using COUNT function

Let say I want to calculate number of orders made by each customer in store. Then we need to get the count of orders per customer. Below mentioned will be the query.

SELECT CustomerId, COUNT(*) AS "Number of order"
FROM Order
GROUP BY CustomerID;

No comments:

Post a Comment