You will want to use the DATE_FORMAT
function in MySQL to format the date for each query.
-- query 1
select date_format(date, '%m-%d') `m-d`,
sum(amount) Total
from mytable
group by date_format(date, '%m-%d');
See SQL Fiddle with Demo. Gives a result:
| M-D | TOTAL |
-----------------
| 04-08 | 50 |
| 05-01 | 200 |
| 05-03 | 400 |
-- query 2
select date_format(date, '%Y-%m') `Y-m`,
sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');
See SQL Fiddle with Demo. Gives the result:
| Y-M | TOTAL |
-------------------
| 2013-04 | 50 |
| 2013-05 | 600 |
Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):
SELECT a.*, c.*
FROM
(
SELECT yr,mn, MAX(translations_per_month) as max_user_translations
FROM
(
SELECT YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id,
COUNT(1) as translations_per_month
FROM `gp_translations` t1
INNER JOIN gp_users u ON
(u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id
)b
GROUP BY yr,mn
)a
INNER JOIN
(
SELECT YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
COUNT(1) as translations_per_month
FROM `gp_translations` t1
INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)
Also, I made a guess that status
column is in gb_users
table ; if not, the query needs to be modified a bit.
Best Answer
This works
With the final query like
Here @month and @year are numeric values from the administrator. Defaults could be
This is the quick and dirty solution applicable for a small table. If performance is an issue, consider putting a binary tree index on the date column or breaking it down into (month, year) with a hash index.
Binary tree index allows comparison like so
But this does not work when @month=12 unless you do some complicated date math, which can be done elsewhere. Note that you can do concatenation elsewhere too, to enable query caching.
With hash index