Combine two MySQL Count Queries

Question

I have a table with columns: NAME, CHANGE_ID, and CHANGE_DATE, where each row constitutes a single change, 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.

FrustratedWithFormsDesigner · Accepted Answer · 2011-03-30 17:36:49Z

1

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

answered Mar 30, 2011 at 17:36

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yottagray Over a year ago

I tacked on ORDER BY date DESC, change_count DESC and it gives me pretty much what I need. I appreciate your help.

Yottagray Over a year ago

Ok I lied. What I actually need it to order by date DESC and then I need to order the names in the same order that they appear in the result of the first query in my question above... any ideas?

Collectives™ on Stack Overflow

Combine two MySQL Count Queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related