Course 9.3Lesson 1 of 3
A query can give one number instead of a list: how many records pass, or the total of a field. COUNT counts the records and SUM adds up a numeric field. You use both with and without WHERE and work out the single value a script outputs.
Try the code and the simulations beside the lesson.
A theatre manager does not want a list of every sale. She asks: how many sales were there on Saturday, and how much money did they bring in? Each answer is one number.
Here is the theatre's table, Tickets. The Amount field is in dollars. We use the table in all three lessons of this course.
SaleID Day Show Seats Amount
T1 Friday Matinee 4 32
T2 Saturday Evening 2 30
T3 Saturday Matinee 6 48
T4 Sunday Evening 3 45
T5 Saturday Evening 5 75
T6 Friday Evening 1 15COUNT(*) counts the records. The star means "count every record", whatever is in its fields.
SELECT COUNT(*)
FROM Tickets;The output:
COUNT(*)
6With WHERE, it counts only the records that pass.
SELECT COUNT(*)
FROM Tickets
WHERE Day = 'Saturday';The output:
COUNT(*)
3You may also meet COUNT(Field), such as COUNT(SaleID). It counts the records that have a value in that field. When no field is empty, as in this course, it gives the same number as COUNT(*).
SUM(Field) adds up the values of a numeric field.
SELECT SUM(Seats)
FROM Tickets;The output:
SUM(Seats)
21With WHERE, it adds up only the records that pass. Here are the Saturday takings.
SELECT SUM(Amount)
FROM Tickets
WHERE Day = 'Saturday';The output:
SUM(Amount)
153Check it by hand: the Saturday sales are , and dollars, and dollars.
COUNT(*).Seats field: SUM(Seats).These two questions sound alike but give different numbers. On Saturday there were sales, but seats.
SUM needs a field of numbers, such as an integer or a real field. Adding up names makes no sense.
When nothing passes. If no record meets the condition, COUNT(*) gives . SUM gives an empty value (called NULL), which Python prints as None.
The program builds the Tickets table and prints four single numbers: two counts and two sums. fetchone()[0] takes the one value out of the one row of output.
SUM(Seats), not COUNT(*).COUNT(*) counts the records that pass.SUM(Field) adds up a numeric field of the records that pass.Try each one on your own first. If you are stuck, open a hint. Then check with Show the answer.
SELECT COUNT(*) FROM Tickets WHERE Show = 'Matinee'; output?SELECT SUM(Seats) FROM Tickets WHERE Show = 'Evening'; output?SUM(Day) make no sense?Every lesson is open to read. Enrolling also lets you:
Log in to join the discussion.
Loading…
This lesson is free to read. Enroll free to keep your place and take the tests.