How to combine SUM() function with WHERE() clause in SQL

Overview

In SQL, we use the SUM() function to add the numeric values in a column. It is an aggregate function in SQL. The aggregate function is used in conjunction with the WHERE clause to extract more information from the data.

Syntax

The syntax for this is as follows:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example

Let’s assume we have a table called Person with columns such as name, salary, state, and gender.

Now, we want to get the sum of the salary paid to people living in Lagos.

How do we extract this information from our table?

We use the following code to use the SUM() function with the WHERE clause in SQL.

CREATE TABLE Person (
    ID int,
    name varchar(100),
    salary int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Person
VALUES (1,'Sharon Peller',40000,'Female','Kogi');
INSERT INTO Person
VALUES (2,'Paul Dons',150000,'Male','Lagos');
INSERT INTO Person
VALUES (3,'Ameera Abedayo',200000,'Female','Imo');
INSERT INTO Person
VALUES (4,'Maria Elijah',320000,'Female','Lagos');
INSERT INTO Person
VALUES (5,'David Hassan',250000,'Male','Abuja');
INSERT INTO Person
VALUES (6,'Niniola Disu',80000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Praise Dominion',340000,'Female','Lagos');
INSERT INTO Person
VALUES (7,'Divine Favour',280000,'Female','Abuja');
INSERT INTO Person
VALUES (7,'Praise Dominion',100000,'Female','Lagos');
INSERT INTO Person
VALUES (8,'Joe Smith',75000,'Lagos');
-- Query 
SELECT SUM(salary)
FROM Person
WHERE state = 'Lagos';

Explanation

In the code above:

  • Line 1–7: We create a table called Person with columns id, name, salary, gender, and state.
  • Line 10–29: We insert data into the Person table.
  • Line 31–35: We find the sum of the salary paid to people living in Lagos using the SUM() function with the WHERE() clause.

Free AI Mock Interviews
Coding Interview
Coding PatternsFree Interview
Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.
System Design
You TubeFree Interview
Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)