Fulfledge

Add up and count

INPUT · Slides

Calculating as you pull data out

01 / 05

You want a value the table does not hold

sales has price (the price of one) and sold (how many went). But what you want to know is how much money that item brought in.

The multiplied column is not in the table. Should it be? No — you can work it out as you pull the data out.

02 / 05

Calculating inside SELECT

What you line up in SELECT does not have to be column names — it can be an expression. Write price * sold and back comes the multiplication for each row, as one column.

* is the multiply sign, the same as in JavaScript.

SELECT item, price * sold  FROM sales;

Result

mug | 3000
wooden spoon | 3600
flower brooch | 4200
cloth bag | 7200
coaster | 8400
glass dish | 5400
ceramic vase | 4800
leather bookmark | 4800

03 / 05

The heading is the expression you wrote

The heading of a calculated column comes out as price * sold — exactly what you wrote. You have not learned how to give it a name of your own yet, so it stays as it is.

Which means changing how you write the expression changes the heading. When you compare against the answer here, write it the way the question does.

04 / 05

Sorting by a calculated column

You can put the same expression in ORDER BY. Sort by takings biggest first and the top earner leads.

An expression can go in WHERE too. Remember it as: anywhere a column name can go, an expression can go.

SELECT item, price * sold  FROM sales  ORDER BY price * sold DESC;

Result

coaster | 8400
cloth bag | 7200
glass dish | 5400
ceramic vase | 4800
leather bookmark | 4800
flower brooch | 4200
wooden spoon | 3600
mug | 3000

05 / 05

The signs you get, and the empty-value trap

You have +, -, * and /. With subtraction you can ask "what if we knocked 100 off?"

But any calculation with an empty value in it comes out empty. rating + 1 stays empty on the rows where no rating was written. Let us write some.

SELECT item, rating + 1 FROM sales;

Result

mug | 6
wooden spoon | 5
flower brooch | 
cloth bag | 
coaster | 4
glass dish | 6
ceramic vase | 5
leather bookmark | 4