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

CodePudding user response：

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

Live example

Read more on default date time functions here

CodePudding user response：

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

CodePudding user response：

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;