Doing a Sum on a Subset in MySQL

Sometimes I need to sum up a subset of values from a table for a certain column.  For instance,  I may have a table names TABLE and a column that contains is an int and is labeled numberTries.  I may also have another column that is an auto-increment column (int) labeled rownum.  I would like to see the sum of the number of tries for the last 10 rows.  In order to do this, you have to create a query like the following:

SELECT SUM(numberTries) FROM (SELECT numberTries FROM TABLE ORDER BY rownum DESC LIMIT 10) as subquery;

Notice that I have the query to get the rows that we need wrapped by the query to sum up the rows for column numberTries.

One other thing we can do is find out the number of rows that were actually added in there.  If there were not 10 rows, then only the amount that were in the database would have been summed up.

Here is the query to get that number back:

SELECT SUM(numberTries), COUNT(*) FROM (SELECT numberTries FROM TABLE ORDER BY rownum DESC LIMIT 10) as subquery;

Using Limit in MySQL

There are times when you would like to limit the results coming back on a query to certain amount of records.  For instance, if I need to see the last 10 records in a table, I could use the following query:

SELECT * FROM TABLE ORDER BY rownum DESC LIMIT 10;


The previous line would order the records in the table by the row number is descending order and return only the last 10 in the table.  Simple enough.

Now, let's say that we want to get back the 11th-20th records.  We can use LIMIT for this purpose as well.  This would be the query for that:

SELECT * FROM TABLE ORDER BY rownum DESC LIMIT 10,10;


So, the LIMIT keyword can be very useful in getting subsets back for queries.