3

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:

ID Event_Name URL Value
1 EV1 website/ 500
11 EV1 website/ 500
3 EV2 website/two 500
4 EV2 website/two 500
6 EV2 website/four 500
8 EV2 website/six 500
5 EV3 website/three 500
7 EV3 website/five 500
9 EV3 website/four 500
2 EV4 website/one 500
10 EV4 website/eight 500
12 EV4 website/ 500

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:

ID Event_Name URL Value Count
1 EV1 website/ 500 2
11 EV1 website/ 500 2
3 EV2 website/two 500 2
4 EV2 website/two 500 2
6 EV2 website/four 500 1
8 EV2 website/six 500 1
5 EV3 website/three 500 1
7 EV3 website/five 500 1
9 EV3 website/four 500 1
2 EV4 website/one 500 1
10 EV4 website/eight 500 1
12 EV4 website/ 500 1

Hopefully someone can help. Thank you.

Improve this question

1 Answer 1

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi @lemon .I've tested that but it seems to be counting the total number of instances of the event_name not the number of instances of that particular URL within that particular event_name.
I got a typo in the partition by, try now.
Brilliant that's done the job exactly as I had expected. Much appreciated!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.