MySQL - Count only if unique

Question

I have a table with object show/click statistics.
Table has such columns: id, objectId, pageUrl, event('show' or 'click').
For example:

id, objectId, pageUrl,  event  
1,    1,      /cars/,  show  
2,    1,      /cars/,  show  
3,    1,      /items/, show  
4,    2,      /cars/,  show  
5,    2,      /items/, show

Can I, in a simple way, get the count for each object having a unique pageUrl?

The result for dataset must be:

objectId, counter  
1,        2  
2,        2

For object with id 1 there are 3 records, but pageUrl /cars/ appears two times, so counter must be only for unique urls.

Tim Biegeleisen · Accepted Answer · 2015-03-30 08:25:07Z

5

Try this query:

SELECT objectId, COUNT(DISTINCT(pageUrl)) AS 'counter'
FROM Table
GROUP BY objectId

answered Mar 30, 2015 at 8:25

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

Loko · Accepted Answer · 2015-03-30 08:30:01Z

0

You can use group by and count like this:

SELECT count(distinct pageUrl) from object group by objectId

answered Mar 30, 2015 at 8:30

Loko

6,70914 gold badges52 silver badges81 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

You also need to SELECT the objectId per the question.

Loko Over a year ago

@TimBiegeleisen Yeah of course. I do assume OP knows how to select other columns. Just a little example. I see your answer has the result the OP wants. +1

Rigel1121 · Accepted Answer · 2015-03-30 08:35:14Z

0

First, I generated the inner select query to group the records with the same objectId and pageUrl. Lastly, I counted the pageURL per objectId.

SELECT 
      objectId, 
      COUNT(pageUrl) 
FROM (SELECT objectId,pageUrl FROM table GROUP BY objectId,pageUrl) z 
GROUP BY objectId

answered Mar 30, 2015 at 8:35

Rigel1121

2,0171 gold badge19 silver badges25 bronze badges

Comments

gia · Accepted Answer · 2015-03-30 08:43:53Z

0

SELECT objectId, COUNT(*) AS count FROM ( SELECT objectId,pageUrl FROM table GROUP BY objectId,pageUrl ) z GROUP BY objectId

edited Mar 30, 2015 at 8:43

answered Mar 30, 2015 at 8:29

gia

7575 silver badges19 bronze badges

1 Comment

Joël Salamin Over a year ago

I suggest you to use aliases in order to avoid ambiguity

Collectives™ on Stack Overflow

MySQL - Count only if unique

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related