count based on two columns in Mysql

Question

My table in Mysql is like this:

 IP       ID     area
 aaa      A        I
 aaa      A        I
 aaa      B        I  
 aaa      C        II
 bbb      A        I
 bbb      B        III
 ccc      B        I

Now I want to calculate for each area value, how many different (IP, ID) pairs are there?

How can I use both IP and ID as a whole dataset to do this?

I don't think the following query is correct:

SELECT area, count (distinct IP, ID) from video GROUP BY area ORDER BY COUNT (distinct ip, ID)

So does anyone know how to do it?

konsolenfreddy · Accepted Answer · 2012-01-11 18:10:04Z

1

SELECT count(*), CONCAT_WS(' ',IP,ID) as yourTitle FROM video GROUP BY yourTitle

should do

answered Jan 11, 2012 at 18:10

konsolenfreddy

9,7091 gold badge28 silver badges36 bronze badges

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

Comments

PinnyM · Accepted Answer · 2012-01-11 18:12:17Z

0

SELECT area, count(*) as distinctCount FROM 
  (SELECT DISTINCT IP, ID, area FROM video) distinctVideos 
GROUP BY area
ORDER BY count(*)

answered Jan 11, 2012 at 18:12

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Collectives™ on Stack Overflow

count based on two columns in Mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related