SQL Sum()

The SUM() function returns the Total sum of a numeric column.

Syntax :

SELECT SUM(column_name) FROM table_name


The "Custmast" table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example :


SELECT SUM(Qty) as Total  FROM Custamst

Total

330

SQL Avg

The AVG() function returns the average value of a numeric column.

Syntax 
SELECT AVG(column_name) FROM table_name

The "custmast"  table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
6	Vicky	Srivilliputtur	60	30

Example : 

SELECT AVG(Rate) AS AvgRate FROM Custmast

Output :

AvgRate

77.666666

SQL Count

The COUNT() function returns the number of rows that matches a specified criteria.

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:


Syntax 


SELECT COUNT(column_name) FROM table_name

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example : 1

SELECT count(*)  as NoofRecord FROM Custmast

Output :

NoofRecord

6

Example : 2

SELECT count(Custname)  as NoofRecord FROM Custmast

Output : 

NoofRecord

6

Example : 2

SELECT count(Distinct Custname)  as NoofRecord FROM Custmast

Output : 

NoofRecord

5

    * Distinct Same name  or  Dublicate Record Not Allowd ( custname --> kodee two time appear in the table)

SQL Between

The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.


Syntax

  SELECT column_name(s)
  FROM table_name
  WHERE column_name
  BETWEEN value1 AND value2



The "Custmast" Table  ( Note Date Format ( MM/DD/YYYY)

custcode	Date	custname	city	rate
1	02/22/2008	Siva	Srivilliputtur	70
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200
4	02/04/2011	Vijay	Chennai	30
5	07/08/2011	Kodee	Sandnes	80
5	08/06/2012	Kodee	Srivilliputtur	30

Example: 1

SELECT     *

FROM         custmast

WHERE     Date BETWEEN '01/01/2009' and '6/08/2011'

Output :

custcode	Date	custname	city	rate
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200
4	02/04/2011	Vijay	Chennai	30

Example: 2

SELECT     *

FROM         custmast

WHERE     custcode BETWEEN 1 and  3

Output :

custcode	Date	custname	city	rate
1	02/22/2008	Siva	Srivilliputtur	70
2	06/08/2009	Bala	Sivakasi	56
3	02/15/2010	Kanna	Madurai	200

SQL Max() and Min()

The MAX() function returns the largest value of the selected column.

Syntax 


        SELECT MAX(column_name) FROM table_name

 The "custmast" table

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example : 

SELECT MAX(Qty) as MaxQty FROM custmast

MaxQty

100

SQL GROUP BY

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax 

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name 


The "Custmast" table 

custcode	custname	city	qty	rate
1	Siva	Srivilliputtur	50	70
2	Bala	Sivakasi	30	56
3	Kanna	Madurai	100	200
4	Vijay	Chennai	30	30
5	Kodee	Sandnes	60	80
5	Kodee	Srivilliputtur	60	30

Example :


Now we want to find the total sum (Qty) of each customer.

Select Custname,Sum(Qty)  as Tot From Custmast 

Group by CustName

custname	Tot
Siva	50
Bala	30
Kanna	100
Vijay	30
Kodee	120

Group By More than One Column

SELECT Custname,City,SUM(Qty) as Tot FROM Orders
GROUP BY Custname,City