I have the following query:

Select ID,Event_Name,MAX(Page_URL) as URL,MAX(Value) as Value from database
Group by ID,Event_Name
Order by Event_Name

Which outputs the following data:

However, I'd like to introduce a count column which will count all the instances of the URL in the query based on their Event_Name. I would like this to output like this:

Hopefully someone can help. Thank you.

CodePudding user response：

Try adding up a COUNT window function as follows:

WITH cte AS (
    Select ID,Event_Name,MAX(Page_URL) as URL,MAX(Value) as Value from database
    Group by ID,Event_Name
    Order by Event_Name
)
SELECT *, COUNT(URL) OVER(PARTITION BY Event_Name, URL) AS cnt
FROM cte