SQL In Simple English Part 1/2 - Mathematical functions in SQL
(Page 4 of 5 )
There are many simple operations that you can do in order to formulate some useful information from a database rather than getting simple records from the database.
Here are a few examples of these mathematical operations:
SELECT AVG(age) FROM people Would return 1 value corresponding to the average age of all the persons that exist in the table people.
SELECT AVG(age) FROM people WHERE age>30 Same as the previous query, except it gets the average age of all people over 30.
SELECT MAX(age) FROM people Returns the maximum age among all the persons in the table people.
SELECT MIN(age) FROM people Returns the minimum age among all the persons in the table people.
SELECT SUM(age) FROM people WHERE age>20 Returns the total sum of all the ages of the persons whose age is above 20 from the table people.
Ok, here are two new things that I have used only a few times in my programs, but they maybe useful to you, so I shall talk about them. There are two keywords called GROUP BY and HAVING. Both of these are used in conjunction with aggregate statements like SUM , AVG , etc..
For all the examples in this article we would be using a sample table that is shown below:
The GROUP BY keywords has been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.
SELECT name, SUM(profit) FROM companies Returns a recordset with 3 records. Each record has 2 values. The first record would have the value 'Sega' and '85000'. The second record would have the values 'Microsoft' and '85000', and the third record would have the values 'Sega' and '85000'. Thus it is clear that this is not what was required. There is no sense in getting the sum of all the profits of all the companies along with each company name. What would be acceptable is the sum of all the profits of the respective companies along with that company's name. Read the next statement, which we can get like this:
SELECT name, SUM(profit) FROM companies GROUP BY name Each record has 2 values. The first record would have the value 'Sega' and '35000'. The second record would have the values 'Microsoft' and '50000'.
The HAVING keyword has been added to SQL because a WHERE keyword can not be used against aggregate functions (like SUM). Without the HAVING keyword it would not be possible to test for function result conditions.
SELECT name, SUM(profit) FROM companies GROUP BY name HAVING SUM(profit)>40000 The query above returns a recordset with 1 record. This record would have 2 values, namely 'Microsoft' and '50000'. Since the sum of the profits of the company by the name 'Sega' is only 35000 (which is lesser than 40000 as required in the Query).
SELECT Company "Firm", Amount "Profit" FROM Sales The query above uses field name alias. It returns the two columns with the headings as "Firm" and "Profit" instead of "Company" and "Amount".
As far as queries are concerned, you have to include the column names that exist in the database table in your query, but you can also include an alias with which you can carry on further work with the returned results by the database. Let’s see an example of using an alias now:
SELECT name "firm", profit "riches" FROM companies Would return a recordset consisting of 3 records each with 2 values. Basically all of the 3 records from the sample database would be returned, but the column names would be changed to those that were mentioned in the SQL statement. Thus name would be changed to firm and profit would change to riches.
Next: Conclusion >>
More MySQL Articles
More By Kiran Pai
