CODEWITHSUNDEEP
phpmysql
pghalcrow
pghalcrow

Reputation: 23

mysql count distinct rows by another column and group results

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).

Upvotes: 2

Views: 1148

Answers (1)

Blank
Blank

Reputation: 12378

Use count distinct:

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

Upvotes: 1

Related Questions