SQL

Creating views to get easy access to frequent queries


Problem

When your system gets more complicated you might need to do some calculations using SQL queries. While it's usually pretty simple to do basic math using SQL, you'll probably still not want to write the same equations over and over. Views can be pretty handy when you need to run the same queries multiple times.



Solution

Assuming we've got a table like this:

product qty price_per_item
broom 2 20
table 1 150

You might want to know the total of each line in multiple places, the query would look like this:

SELECT product, qty, price_per_item, qty * price_per_item total_price FROM products

Now that we have the query we just need to prepend a few things and we've got ourselves a view:

CRATE VIEW product_totals AS
SELECT product, qty, price_per_item, qty * price_per_item total_price FROM products

With this view you can now query the result with a simple query like this:

SELECT product, total_price FROM product_totals

Comments




Please sign-in to post a comment