I created a MySQL table using the following query:

---------------------------------------------------------------------

SELECT
DATE_FORMAT(`date`, '%M %Y') AS `MONTH`,
DATE_FORMAT(`date`, '%Y%m') AS `DATEORDER`,
DATE_FORMAT(`date`, '%Y') AS `YEAR`,
SUM(`amount`) AS `AMOUNT`,
SUM(`balance`) AS `BALANCE`
FROM `business`
GROUP BY DATE_FORMAT(`date`, '%M %Y'), DATE_FORMAT(`date`, '%Y%m'), DATE_FORMAT(`date`, '%Y')

ORDER BY DATE_FORMAT(`date`, '%Y%m')

----------------------------------------------------------------------

PROBLEMS ENCOUNTERED
========================

1. I added a filter for the YEAR column but it doesn't work. Never returns any result.

2. Totals for the AMOUNT and BALANCE columns do not work. They always show zero.

ANOTHER BUG

=============

In another table (also MySQL), I created a column for a client listing containing a URL with the structure:

http://localhost/clients/client/?id=[NUMBER]||[NAME]

(eg http://localhost/clients/client/?id=345||JOHN MASTERS)

Sorting on that column does not work correctly. The plugin fails to pick the name alone and use it to sort, but uses the number in the URL. This is not the expected behaviour.

Thanks for the reply. I followed your instructions and there is progress at last.

Disabling server side processing on MySQL data sources solves the problem of totals and averages on the columns, although I do not quite understand why the plugin is made this way. Perhaps you should look into this in a future plugin update since it becomes a problem with large data sets.

I created VIEWS in MySQL and everything worked perfectly, even with server processing enabled. 

As for URL sorting, I suggest you consider updating the plugin code to enable sorting using the text part of the URL. It shouldn't be too difficult to write code to extract the text part of the URL element, use it to sort the data and then unset it afterwards. I do that all the time with PHP arrays.

As it is, I have been forced to create a hidden column to use for searching but I cannot use it for sorting on the table. I have hyperlinked text that is not sortable :-(

@Bogdan,

Much as I have been able to get all my complex queries working using views, I feel that your documentation really needs to be much clearer on this issue. For example when a user is creating a table for the first time, on the 5 options given, you can mention complex queries or you can put a standalone option to make the choices 6. Your online guide should  explicitly and clearly mention complex queries and the effect they have on filtering, sorting, summing, etc.

I wasted too much time troubleshooting when a simple instruction could have solved everything quickly. And I believe Server side processing should always work, regardless of how complex the the query is since the returned data from MySQL is the same for both cases. When I tinkered with the DataTables jQuery plugin earlier, I was able to get server side processing working (by editing the script that generates the JSON data) even with complex queries.

Looking forward to sorting function on URLs.