The TSQL

##
With Single Expression

##
Using TSQL DISTINCT with SUM

##
SUM in Formula

##
Using TSQL GROUP BY with SUM

**SUM function**is used to return the sum of an expression in a SELECT statement. The syntax for the TSQL**SUM function**is:`SELECT SUM(`*expression*)

`FROM tables`

`WHERE conditions;`

##
With Single Expression

For example, you might wish to know how the
combined total salary of all employees whose salary is above $25,000 / year.

`SELECT SUM(salary) AS "Total Salary"`

`FROM employees`

`WHERE salary > 25000;`

In this TSQL SUM Function example, we've

**aliased**the SUM(salary) expression as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.##
Using TSQL DISTINCT with SUM

You can use the

**TSQL DISTINCT clause**within the TSQL SUM function. For example, the TSQL SELECT statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year.`SELECT SUM(DISTINCT salary) AS "Total Salary"`

`FROM employees`

`WHERE salary > 25000;`

If there were two salaries of $30,000/year, only
one of these values would be used in the TSQL SUM function.

##
SUM in Formula

The

*expression*contained within the TSQL SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses.`SELECT SUM(income - expenses) AS "Net Income"`

`FROM gl_transactions;`

You might also want to perform a mathematical
operation within the TSQL SUM function. For example, you might determine total
commission as 10% of total sales.

`SELECT SUM(sales * 0.10) AS "Commission"`

`FROM order_details;`

##
Using TSQL GROUP BY with SUM

In some cases, you will be required to use the

**TSQL GROUP BY clause**with the TSQL SUM function.
For example, you could also use the TSQL SUM
function to return the name of the department and the total sales (in the
associated department).

`SELECT department, SUM(sales) AS "Total sales"`

`FROM order_details`

`GROUP BY department;`

Because you have listed one column in your

**TSQL SELECT statement**that is not encapsulated in the TSQL SUM function, you must use the**TSQL GROUP BY clause**. The department field must, therefore, be listed in the TSQL GROUP BY section.
## No comments:

## Post a Comment