CODEWITHSUNDEEP
mysqlsql
Александр Муляр
Александр Муляр

Reputation: 118

MySQL - Count only if unique

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.

Upvotes: 0

Views: 83

Answers (4)

gia
gia

Reputation: 757

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

Upvotes: 0

Rigel1121
Rigel1121

Reputation: 2016

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

Upvotes: 0

Loko
Loko

Reputation: 6679

You can use group by and count like this:

SELECT count(distinct pageUrl) from object group by objectId

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Try this query:

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

Upvotes: 5

Related Questions