SUM Function in SQL

What are the Aggregate Functions in SQL?

Aggregate functions are the built-in methods that help us to convert multiple values available in the column to a single entity.

These are required to combine and draw insights from the data spread in columns of the database. Aggregate functions can also be applied to the expressions.

Some of the most popular SQL aggregate functions are as follows:

Sr.No Function Description
1 AVG() Returns the average value.
2 COUNT() Returns the count.
3 MAX() Returns the max out of given values.
4 MIN() Returns minimum out of the given values.
5 SUM() Returns the sum of the given values.
6 VAR() Returns variance of the given values.

Where do we use the SUM Function in SQL?

We use the Sum function wherever we need to find the total of an expression or a column.

  1. SUM(colName):

    Returns the sum of all values in the column colName.

  2. Syntax:

    SELECT SUM(col1) AS alias,
    Col2 , col3,.....
    FROM tableName;
    
  3. Sum(DISTINCT colName):

    Returns the sum of distinct non-null values in the column colName.

    Syntax:

    	SELECT SUM(DISTINCT col1) AS alias,
    Col2 , col3,.....
    FROM tableName;
    

Demo DataBase

Let us now view our demo database and understand it’s contents.

Query:

SELECT * FROM IBM;

In our IBM database, we have five columns indicating the details of all employees at IBM. The columns available are as follows:

  • Name
  • Age
  • Employee_id
  • Location
  • Experience

Let us now work on examples using the IBM database and try to understand the Sum() function in SQL.

Example 1: Let us find the total of age of all employees.

Query:

SELECT SUM(age) as totalAge FROM IBM;

Example 2: Let us now find the sum of distinct age of employees from our IBM database.

Query:

SELECT SUM(DISTINCT age) as totalAge FROM IBM;

Example 3: Let us now find the sum of age of employees location wise.

Query:

SELECT location as Current_Location ,

SUM(DISTINCT age) as LocationWiseAge FROM IBM

group by location;

Example 4: Let us now find the sum of employees at Pune and Indore office respectively.

Query:

SELECT location as Current_Location ,

SUM(DISTINCT age) as LocationWiseAge FROM IBM

Where location=’Pune’ or location =’Indore’

group by location;


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.