Question 21712 in Mysql Question posted in
The official MySQL documentation can be found here.

Count unique event per month – Mysql

Im trying to find the total count of event names. My table has a column Royalty_Month and Event_Combined. If an event appears in Month A, I dont want to count it in Month B.

This is the query I have, but its not excluding events that occurred in the previous months.

SELECT
    Royalty_Month,
    COUNT(DISTINCT(Event_Combined)) As Event_Count
    FROM Agency   
    GROUP BY Royalty_Month
    ORDER BY Royalty_Month ASC

Sample Data:

Royalty_Month Event_Combined
2010-05-01 Event A
2010-06-01 Event B
2010-07-01 Event C
2010-07-01 Event B
2010-07-01 Event D
2010-07-01 Event D
2010-07-01 Event D
2010-07-01 Event E
2010-07-01 Event E
2010-07-01 Event E
2010-07-01 Event E
2010-07-01 Event E
2010-08-01 Event F
2010-08-01 Event F
2010-09-01 Event E
2010-09-01 Event E
2010-09-01 Event G
2010-09-01 Event G
2010-09-01 Event G
2010-09-01 Event H
2010-09-01 Event H
2010-09-01 Event H
2010-10-01 Event E
2010-10-01 Event F
2010-10-01 Event G
2010-10-01 Event G

Expected Output:

Royalty_Month Total_Events
2010-05-01 1
2010-06-01 1
2010-07-01 3
2010-08-01 1
2010-09-01 2
2010-10-01 0
Tags:

3

Answers


  1. 0

    If you want to count rows by month, use default group by month – this will group all rows which fall in a single month.

    To fetch the count of rows in a single month, your query should be

    SELECT Royalty_Month, count(Event_Combined) as total FROM agency
group by Royalty_Month, MONTH(Royalty_Month) // full group by
ORDER BY Royalty_Month ASC

    which will generate

    enter image description here

    Live example

    Read more on default date time functions here

    Login or Signup to reply.
  2. 0

    In MySQL 8.0, one option is to:

    • retrieve first time an event appears, using a ranking value given by the ROW_NUMBER window function
    • count only first appearances for each day, with a conditional sum of 1s
    WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY Event_Combined ORDER BY Royalty_Month) AS rn
    FROM tab
)
SELECT Royalty_Month,
       SUM(IF(rn = 1, 1, 0)) AS Total_Events
FROM cte
GROUP BY Royalty_Month
    Login or Signup to reply.
  3. 0

    you can try this on previous versions as well as new version of MySQL:

    SELECT  CurrMonth,  SUM(CASE WHEN Event_PrevMonth IS NULL THEN 1 ELSE 0 END)TOTAL_EVENTS
FROM
(
    SELECT DISTINCT A1.Event_Combined, A1.Royalty_Month CurrMonth, A2.Event_Combined Event_PrevMonth , A2.Royalty_Month Prev_month
    FROM Agency A1
    LEFT OUTER JOIN
    (
        SELECT Event_Combined, Royalty_Month 
        FROM Agency
    )A2 ON A1.Event_Combined = A2.Event_Combined AND A1.Royalty_Month > A2.Royalty_Month
) T 
GROUP BY CurrMonth;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search