mysql count distinct rows by another column and group results

Question

I have a table of searches logged. The columns are IP, query, timestamp. What I want is to group the data by "query" and count the number of IP address unique instances for that "query". Here's a sample of that table:

TABLE: SEARCHES
IP    |    query    |     timestamp
1.2.3.4    oranges        1485215625
1.2.3.5    bananas        1485215643
1.2.3.4    oranges        1485215655
1.2.3.6    apples         1485215662
1.2.3.7    oranges        1485215711
1.2.3.7    pears          1485215730

I am using the following mysql query:

SELECT query, COUNT(query) as num FROM searches WHERE timestamp > ".$sometimestamp." GROUP BY query

My goal would be to retrieve the following data:

oranges [2]
bananas [1]
apples  [1]
pears   [1]

My query is close, but it would produce:

oranges [3]  <-- IP address 1.2.3.4 searched for this twice, counting 3  total
bananas [1]
apples  [1]
pears   [1]

I need a way for it to only count a unique search query once per IP address (but not ignore multiple unique queries from the same IP address).

Blank · Accepted Answer · 2017-01-24 01:40:51Z

1

Use count distinct:

SELECT 
    `query`, 
    COUNT(DISTINCT `IP`) as num 
FROM `searches`
WHERE `timestamp` > ".$sometimestamp."
GROUP BY `query`

edited Jan 24, 2017 at 1:40

answered Jan 24, 2017 at 1:37

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

mysql count distinct rows by another column and group results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related