CODEWITHSUNDEEP
mysqlsql
leoarce
leoarce

Reputation: 549

mysql select unique users in table and count them multiple times based on a column

If I have a table like this:

id | vid  | cid   | ipaddress 
 1 | 1    | 1     | 1.1.1.1
 2 | 1    | 1     | 1.1.1.1
 3 | 1    | 1     | 1.1.1.2
 4 | 2    | 1     | 2.2.2.2
 5 | 2    | 1     | 2.2.2.2
 6 | 2    | 1     | 2.2.2.2

vid is visitor id and cid is campaign id

If visitor visits campaign the visit gets captured to table. I want to then display a the number of unique visits, not based on user, but on user and ip. So for the data above it's 2 users and 1 of the users visited with 2 different ip addresses, so how do i do a select query to select distinct visitors, but count the visitors multiple times if they used multiple ip addresses? The total number that should display for above captured data is 3 unique visits.

Upvotes: 1

Views: 1785

Answers (2)

Akshay
Akshay

Reputation: 2229

Well you can try this :-

SELECT vid, count(distinct(IP)) AS dstn from test

enter image description here

Upvotes: 3

SMA
SMA

Reputation: 37023

Try something like:

SELECT vid, COUNT(DISTINCT ipaddress) AS ip_address_count
FROM mytable
GROUP BY vid

Upvotes: 1

Related Questions