CODEWITHSUNDEEP
mysqlsql
imAnIdiot123
imAnIdiot123

Reputation: 23

Select count of distinct column value, excluding duplicate values of another column

sorry for the confusing title. Let me elaborate. I have a table called searches. This table has a search_term column and a customer_id column. Each row represents a value (search_term) that a customer (customer_id) has searched.

search_term | customer_id
'test'      |           1
'test'      |           1
'test'      |           2
'test'      |           3
'test 2'    |           1
'test 2'    |           2

I am interested in finding out how many times a distinct search_term value appears. This is easy enough.

SELECT
    Lower(s1.`search_term`) AS 'Search Term',
    COUNT(s1.`search_term`) AS 'Number Of Requests'
FROM `search_logs` AS s1
GROUP BY Lower(s1.`search_term`)
ORDER BY `Number Of Requests`  DESC

The results of this query would be:

Search Term | Number Of Requests
'test'      |                  4
'test 2'    |                  2

The tricky part (for me) is that I do not want to include duplicate searches by the same customer. So, that means that the desired results from this test data would be:

Search Term | Number Of Requests
'test'      |                  3
'test 2'    |                  2

(Because the customer_id = 1 searched for 'test' twice.)

I bet the answer is probably very simple, but I'm still a novice so I apologize! My best attempt at the correct query is:

            SELECT
                Lower(s1.`search_term`) AS 'Search Term',
                COUNT(s1.`search_term`) AS 'Number Of Requests'
            FROM `searches` AS s1
            JOIN `searches` AS s2
                ON s1.`search_term` = s2.`search_term`
                AND s1.`customer_id`!= s2.`customer_id`
            ORDER BY `Number Of Requests` DESC

The results from this query are definitely not right..

Thanks for the help!!

Upvotes: 0

Views: 913

Answers (1)

sammywemmy
sammywemmy

Reputation: 28644

Let me know if I'm missing something : You could use count with distinct

 select search_term, count(distinct customer_id) as number_of_requests
 from table 
 group by search_term

Upvotes: 1

Related Questions