So we've done quite a bit of setup work to get to this point, and in this guide, it's all going to come together. I'm going to start off by asking you a question, and that is if I were to need to know how much revenue per quantity items sold we were making. Could you tell me that?

The answer is yes, but we may need to work through the exact solution and figure out the functions that we're going to need to build it up. So I think it would help if we had a visual. So if I do select all from Guides and if I run this you can see that we have all of our guides.

We have six guides right here, and now they have revenue as well as quantity. Essentially what I want to know is for the total amount of revenue how much does each item generate?

You can imagine that this would be something like an app store where the users sell their guides, and they sell them for less than a dollar all the way through more than a dollar. We want to figure out how much revenue is each one of those bringing up, and we want to see the kind of a breakdown for each one of the titles.

So let's see how we can put that together. I'm going to create a new select statement here, and I'm going to change my syntax just a little bit because I think it's easier to read. We're going to be writing a little bit longer set of query items. I want to find the guide's title, and I want that shown.

Then I also want guides revenue, and then I want to have, and this is the next one is the guides quantity. The next one is kind of where the magic happens, and that is that we can perform regular computation just like we would in a programming language and just like in a calculator. The next time you say guides_revenue divided by guides_qty and then from guides.

This is going to be pretty neat because what this is essentially going to do is it's going to list out the title, revenue, quantity, and none of these are technically necessary. We could go with just a single line, but then it would be hard to read because we'd have no idea the value in which title it is associated with. I also want to list these out so that we can see the math is working.

Now if I hit command and return.

You can see that this is generated a summary report for us but a little bit different. It's not using an aggregate function like before. Like we're not using some Macs or anything like that. Instead, we're just using regular mathematical formulas. So that is what we can do, and it's going to allow us to run our calculations.

Right here we can see that the guide's title my blog made $5000 dollars, and it sold 543 copies. That means that each one of the copies was worth 92 cents. Then come down to "Another one of My Posts". This one made $5000 and sold 552 copies. It made over nine dollars per post.

This is pretty cool. We're able to see with just a few lines of code right here, and we're able to query what the revenue was on a per item basis. So this is something that's incredibly powerful, and you're not limited to only using the division symbol. The full list of symbols and all add appear as a comment, and that is the Asterix to multiply, slash to divide, addition, and subtraction. I'm going to let me select all these make them a comment, and now you can see these are the mathematical operators the basic ones that you can use in SQL.

This is something whenever you need a calculated field that is the formal name for this; this is a calculated field where running a calculation and as you can see the very top of it isn't that clear. It's just pointing out exactly what this does. Then, later on, we're going to talk about aliases and how we can name them something special.

But for right now this is performing a mathematical computation, and it's telling us how much each one of our guides is worth which is something that you will probably be running in your own production applications. So nice work you now know how to implement calculated fields in SQL.

Code

USE devcamp_sql_course_schema;

-- * / + - 
SELECT
guides_title,
guides_revenue,
guides_qty,
guides_revenue / guides_qty
FROM guides;