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.

Illegal Mix of Collations Error in MySQL

I was doing a join statement in MySQL and had an error popup that I had not seen before.  The error i received was the following:

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Since I am trying to keep data segregated, I created a separate database from the one that SugarCRM had created in MySQL and created my own table.  It appears that the 2 table has different collation defaults on them.  I had no idea that was the case.  In order to examine that for each table, I ran the following command:

SHOW CREATE TABLE tblname


This gave me the information I needed.  for each table.  One of my tables showed

COLLATE = 'utf8_unicode_ci'


while the other one did not have a collate value on it.  For me, this meant hat the value was

COLLATE = 'utf8_general_ci'


So, I needed to change the default database collate type for one of them.  I decided to change it to utf8_general_ci and did so with the following command:

ALTER DATABASE dbname COLLATE = 'utf8_general_ci';


I then needed to chance my table.  So, I did this by dropping my table and recreating it.