Combine two MySQL Count Queries
I have a table with columns: NAME, CHANGE_ID, and CHANGE_DATE, where each row constitutes a single c开发者_开发知识库hange, the columns indicated who made the change(name), when it was made(timestamp), and an id for the change(integer).
I can retrieve a list of names sorted by those that have made the most changes(in the last month) with the following query:
SELECT
NAME AS name,
COUNT(DISTINCT CHANGE_ID) AS changes
FROM
CHANGE_TABLE
WHERE
DATE(CHANGE_DATE) > DATE(now() - INTERVAL 1 MONTH)
GROUP BY
name
ORDER BY
changes DESC
And I can retrieve a list of changes made per month in the last 10 months with the following query:
SELECT
DATE_FORMAT(CHANGE_DATE, '%Y-%m') AS date,
COUNT(DISTINCT CHANGE_ID) AS change_count
FROM
CHANGE_TABLE
WHERE
CHANGE_DATE > curdate() - INTERVAL 10 MONTH
GROUP BY
date
What I want is a query that will return the combined information of these queries: I want the names of the top change-makers and how many changes they have made each month for the last 10 months. I don't particularly care how the resulting table looks as long as the data is there. I have wracked my brain, but my SQL understanding is not great enough to solve the problem. Any help would be appreciated.
Have you tried grouping on date and name, something like:
SELECT
DATE_FORMAT(CHANGE_DATE, '%Y-%m') AS date,
COUNT(DISTINCT CHANGE_ID) AS change_count,
NAME
FROM
CHANGE_TABLE, (SELECT
NAME AS name,
COUNT(DISTINCT CHANGE_ID) AS changes
FROM CHANGE_TABLE
WHERE DATE(CHANGE_DATE) > DATE(now() - INTERVAL 1 MONTH)
GROUP BY name
ORDER BY changes DESC
) subq
WHERE CHANGE_DATE > curdate() - INTERVAL 10 MONTH AND change_table.name = subq.name
GROUP BY date, name
精彩评论