Mysql count and count distinct in same query

Question

I have the following 2 tables:

user_table:

user_id
job_id
job_link

job_table:

job_id
click_ip
click_date

I have to count clicks both distinct and all clicks for each job for the current user. Can anybody tell me if this is possible with current table structure and have an answer to this. Thanks.

My current query is also tried with inner join:

SELECT  `user_id` , job.job_id AS job_id,  `job_link`, 
    COUNT( job.click_ip ) AS click_ip
FROM  `job` ,  `user` 
WHERE job.job_id = user.job_id
AND user.user_id =7
GROUP BY job_id

tpolyak · Accepted Answer · 2013-09-08 21:39:43Z

2

COUNT(DISTINCT field) will do the job:

SELECT user_id, 
    job.job_id AS job_id,
    job_link, 
    COUNT(job.click_ip) AS click_ip, 
    COUNT(DISTINCT job.click_ip) AS click_ip_distinct
FROM user JOIN job ON user.job_id = job.job_id
WHERE user.user_id =7
GROUP BY job_id

SQL Fiddle here: http://sqlfiddle.com/#!2/5e03e/2/0

edited Sep 8, 2013 at 21:39

answered Sep 8, 2013 at 21:30

tpolyak

1,2391 gold badge9 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Mysql count and count distinct in same query

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