SQL SUM function

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which SQL statement adds up every value in the Score field of the Results table?

A SELECT SUM(Score) FROM Results;

B SELECT COUNT(Score) FROM Results;

2. How many rows of output does SELECT SUM(Score) FROM Results; give?

A One row for each record in Results

B One row containing the total

3. Which field is suitable for use inside SUM()?

A Price, a real (floating point) field

B Surname, a text field

4. Which statement is written correctly?

A SELECT SUM(Price) FROM Orders;

B SELECT SUM Price FROM Orders;

5. A table has an integer field Goals with the values 2, 0, 3 and 1. What does SELECT SUM(Goals) FROM Matches; output?

A 4

B 6

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT SUM(Score) FROM Results;		AGives the total of a real (decimal) field, returned as a single row
2SELECT SUM(Price) FROM Products;		BSELECT SUM(Quantity) FROM Orders;
3SUM adds up an integer field and gives one row of output		CSUM cannot be used on a text field. Surname is not numeric, so it cannot be added up.
4SUM only works on numeric fields, such as a field of numbers like Mark		DSELECT SUM(Mark) FROM Results;
5SELECT SUM(Surname) FROM Students;		EAdds up every value in the Score field and returns one total
6SELECT SUM Score FROM Results;		FThe field name is missing its brackets. It should be SUM(Score).

Definitions

SUM(field)

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT ___(Score) FROM Results;

SELECT SUM(Price) ____ Products;

______ SUM(Quantity) FROM Orders;

-- Total of the Points field SELECT SUM(______) FROM Players;

-- One total of all Weight values SELECT ___(Weight) FROM Parcels;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT SUM(Score)

B FORM Results

C WHERE Year = 10;

Question 2

A SELECT

B SUM Score

C FROM Results;

Question 3

A SELECT ADD(Score)

B FROM Results

C WHERE Year = 9;

Question 4

A SELECT SUM(Mark)

B FROM Exams

C WHERE Subject = Maths;

Question 5

A SELECT SUM(Price)

B FROM Products

C WHERE Category == 'Tech';

Answer Key

SQL SUM function

Section A: Quick Questions

A — SELECT SUM(Score) FROM Results;

B — One row containing the total

A — Price, a real (floating point) field

A — SELECT SUM(Price) FROM Orders;

B — 6

Section B: Match the Pairs

SELECT SUM(Score) FROM Results; → Adds up every value in the Score field and returns one total

SELECT SUM(Price) FROM Products; → Gives the total of a real (decimal) field, returned as a single row

SUM adds up an integer field and gives one row of output → SELECT SUM(Quantity) FROM Orders;

SUM only works on numeric fields, such as a field of numbers like Mark → SELECT SUM(Mark) FROM Results;

SELECT SUM(Surname) FROM Students; → SUM cannot be used on a text field. Surname is not numeric, so it cannot be added up.

SELECT SUM Score FROM Results; → The field name is missing its brackets. It should be SUM(Score).

Section C: Definitions

SUM(field) — Adds up all the values in a numeric field and returns one total, e.g. SELECT SUM(Score) FROM Results; gives one row. It only works on numbers.

Section D: Fill in the Blanks

SELECT SUM(Score) FROM Results;

SELECT SUM(Price) FROM Products;

SELECT SUM(Quantity) FROM Orders;

-- Total of the Points field
SELECT SUM(Points)
FROM Players;

-- One total of all Weight values
SELECT SUM(Weight)
FROM Parcels;

Section E: Spot the Error

B — The keyword is misspelled. It should be FROM Results, because FROM names the table that SUM reads.

B — SUM needs brackets around the field name. It should be SUM(Score).

A — ADD is not an SQL function. The function that adds up a numeric field is SUM, so it should be SELECT SUM(Score).

C — Text values in a WHERE condition must be in quotes. It should be WHERE Subject = 'Maths';

C — SQL uses a single = to compare values, not ==. It should be WHERE Category = 'Tech';