MySQL Count multiple distinct records with condition

Question

I have a table that records all user cross-profile visits and I need to get the count of distinct visits and the count of new visits to a specified profile (:user_id) since the specified UNIX timestamp (:time).

id  | user_id  | visitor_id |  time (unix)
====+==========+============+=============
  1 |     1    |       5    |  1300000000
  2 |     1    |       5    |  1300000010
  3 |     1    |       8    |  1300000015
  4 |     1    |       9    |  1300000020
  5 |     1    |       9    |  1300000025
  6 |     1    |       9    |  1300000030

So far I was only able to get the count of total visits, but am unable to get the new ones.

SELECT COUNT(DISTINCT v.visitor_id) AS total, SUM(v.time > 1300000012) AS new FROM profile_visits v WHERE v.user_id = 1

returns

total | new
============
   3  |   4

but the required result is

total | new
============
   3  |   2

check the query that i have written below

reggie
– reggie

2011-06-30 18:56:44 +00:00
Commented Jun 30, 2011 at 18:56 — reggie
– reggie, Commented Jun 30, 2011 at 18:56

Bill Karwin · Accepted Answer · 2011-06-30 18:19:38Z

4

You probably want SUM(v.time > 1300000012) AS new.

The expression is either 0 or 1. COUNT() will count 0 as happily as 1. But SUM() will "count" only the 1's.

Re your comment:

SELECT COUNT(t.visitor_id) AS total, SUM(t.subtotal_new) AS total_new
FROM (SELECT v.visitor_id, SUM(v.time > 1300000012) AS subtotal_new
  FROM profile_visits AS v GROUP BY v.visitor_id) AS t

The SUM() of the inner query counts new visits per visitor. The SUM() of the outer query gives the total new visits for all visitors.

edited Jun 30, 2011 at 18:19

answered Jun 30, 2011 at 17:56

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gray Fox Over a year ago

Allow me to correct myself, I was actually using SUM(v.time > 1300000012) and that returns 4 (COUNT returns 6), but I need to get only distinct visitors, since 1300000012 there was one visit from visitor_id 8 and 3 visits from visitor_id 9, but that should count as 2 new visitors.

Gray Fox Over a year ago

I ended up using a bit modified your solution:

SELECT COUNT(t.visitor_id) AS total, SUM(t.subtotal_new) AS total_new FROM (SELECT v.visitor_id, (MAX(v.time) > 1300000012) AS subtotal_new   FROM profile_visits v WHERE v.user_id = 1 GROUP BY v.visitor_id) AS t

Bill Karwin Over a year ago

Okay, I misunderstood your requirements, but I'm glad if I helped you find the right solution.

reggie · Accepted Answer · 2011-06-30 18:57:05Z

2

select * from

((SELECT COUNT(DISTINCT v.visitor_id)  
FROM profile_visits v WHERE v.user_id = 1) AS total,

(SELECT COUNT(DISTINCT v.visitor_id)  
FROM profile_visits v WHERE v.time > 1300000012 and v.user_id = 1) AS new) AS Result

edited Jun 30, 2011 at 18:57

answered Jun 30, 2011 at 18:04

reggie

13.8k13 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

MySQL Count multiple distinct records with condition

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related