How to use COUNT() with where in mysql?

user_7976903 0
Viewed 54

I have tables pages (id, ...) and page_visits (id, page_id, is_subscribed)

The page_visits table now has about 2 million rows per one page_id.

I need to create a query that will grab count of visits and subscriptions per one page. My query only grabs visits count now:

SELECT page_id, COUNT(page_id) AS visits
FROM `page_visits`
WHERE `page_id` in (1, 2)
GROUP BY `page_id`

But how to add here an page_subscriptions column in result that will contains count of rows where is_subscriped = 1 per one page_id?

pivot count group-by mysql sql
3 Answers

count ignores nulls, so you could perform a count over a case expression:

SELECT   page_id, 
         COUNT(page_id) AS visits 
         COUNT(CASE is_subscribed WHEN 1 THEN 1 END) AS subscried_visits
FROM     page_visits
WHERE    page_id IN (1, 2) 
GROUP BY page_id
user_24227760

Just use count(*) and group by is_subscribed as well...

select `page_id`, count(*) as visits, `is_subscribed` from `page_visits` group by `page_id`, `is_subscribed`

You will get 2 rows per page:

  1. With count for subscribers
  2. With count for not subscribers

count(*) does not ignore nulls

user_24639480

I would recommend:

select page_id, count(*) visits, sum(is_subscribed) page_subscriptions
from page_visits
where page_id in (1, 2)
group by page_id

Rationale:

  • I would expect page_id to not be nullable; if so, count(*) is more efficient than count(page_id)

  • is_subscribed seems to be storing 0/1 values - so counting the number of 1s is equivalent to suming the column; this is much more efficient than a conditional count

user_106767160
Related