开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜