CODEWITHSUNDEEP
mysql
Oleksandr IY
Oleksandr IY

Reputation: 3116

get count results of the complicated query

Lets assume I have this sql query

SELECT max(times_viewed) as m, member_id, (select count(viewed_url) from exp_member_page_tracker  where member_id=mpv.member_id)
FROM `exp_member_page_tracker` mpv
group by member_id

I need to count all results. I do it on this way 

SELECT count(member_id) from ( SELECT max(times_viewed) as m, member_id, (select count(viewed_url) from exp_member_page_tracker where member_id=mpv.member_id)
FROM `exp_member_page_tracker` mpv
group by member_id) as m2

But I am not sure how that is efficient. Could you please suggest me how can I count results in the best way?

Thanks

Upvotes: 0

Views: 78

Answers (1)

Nesim Razon
Nesim Razon

Reputation: 9794

For your subquery, am I missing something, Isn't this same result with more effiency? Tryed at sqlfiddle:

http://sqlfiddle.com/#!2/a03cf/9

Yours:

SELECT member_id, max(times_viewed) as max_times_viewed 
, (select count(viewed_url) from exp_member_page_tracker  where member_id=mpv.member_id) as countOfViewedUrl
FROM `exp_member_page_tracker` mpv
group by member_id;

Alternative: 

SELECT member_id, max(times_viewed) as max_times_viewed
, count(viewed_url) as countOfViewedUrl
FROM `exp_member_page_tracker` mpv
group by member_id;

Upvotes: 1

Related Questions