MySQL Show counts without subquery

Question

I have following table

Id	source_url	target_url	event_type
1	googlecom	mypageurl/example	0
2	googlecom	mypageurl/otherpage	0
3	googlecom	mypageurl/example	0
4	googlecom	mypageurl/example	0
5	mypageurl/example	otherdomain/example	1
6	mypageurl/example	otherdomain/example	1
7	mypageurl/otherpage	otherdomain/example	1

Here event_type = 0 is Visit and event_type = 1 is Click

Using following query I can get visits on each url

SELECT
    target_url,
    COUNT(target_url) AS visits
FROM
    tbl_events
WHERE
    event_type = 0 AND target_url <> ''
GROUP BY
    target_url
ORDER BY
    visits
DESC
    ;

Visits

target_url	visits
mypageurl/example	3
mypageurl/otherpage	1

And with following query i can get clicks

SELECT
    source_url,
    COUNT(source_url) AS clicks
FROM
    event_tracking
WHERE
    event_type = 1 AND source_url <> ''
GROUP BY
    source_url
ORDER BY
    clicks
DESC
    ;

Clicks

source_url	clicks
mypageurl/example	2
mypageurl/otherpage	1

Desired Result

I want my final result to be like below without using subquery (since original table contains more than 1 million rows and subquery takes too long)

url	clicks	visits
mypageurl/example	2	3
mypageurl/otherpage	1	1

Right now, I feel like I have to create a temporary table and use ON DUPLICATE KEY UPDATE and use url as primary key, but I feel like there might be a better way and I couldn't figure out.

Is there any way to achieve this result without using subquery and temporary table?

Answer 1

Try this

SELECT URL, 
       SUM(click) click, 
       SUM(VISIT) visit 
FROM (SELECT CASE WHEN event_type=0 
                  THEN target_url 
                  ELSE source_url END      url, 
             SUM(CASE WHEN event_type = 0 
                      THEN 1 
                      ELSE 0 END)          visit, 
             SUM(CASE WHEN event_type = 1 
                      THEN 1 
                      ELSE 0 END)          click
      FROM urlData
      GROUP BY target_url, 
               source_url,
               event_type  ) A 
GROUP BY URL