CODEWITHSUNDEEP
mysqlselectgroup-bydistinct
Adam Tal
Adam Tal

Reputation: 921

mysql query: SELECT DISTINCT column1, GROUP BY column2

Right now I have the following query:

SELECT name, COUNT(name), time, price, ip, SUM(price) 
  FROM tablename 
 WHERE time >= $yesterday 
   AND time <$today GROUP BY name

And what I'd like to do is add a DISTINCT by column 'ip', i.e.

SELECT DISTINCT ip FROM tablename

So my final output would be all the columns, from all the rows that where time is today, grouped by name (with name count for each repeating name) and no duplicate ip addresses.

What should my query look like? (or alternatively, how can I add the missing filter to the output with php)?

Thanks in advance.

[UPDATE]

To minimize confusion, consider this (simplified) db table:

|   name   |   ip   |
---------------------
|  mark    |  123   |
|  mark    |  123   |
|  mark    |  456   |
|  dave    |  789   |
|  dave    |  087   |

The result I'm looking for would be an HTML table looking like this:

|  name    |  name count   |
----------------------------
|  mark    |      2        |
|  dave    |      2        |

What I'm currently getting is:

|  name    |  name count   |
----------------------------
|  mark    |      3        |
|  dave    |      2        |

(it counts mark 3 times, even though two times are with the same ip).

Upvotes: 20

Views: 114549

Answers (5)

knittl
knittl

Reputation: 265339

You can use COUNT(DISTINCT ip), this will only count distinct values, for instance:

SELECT name, COUNT(name), time, price, COUNT(DISTINCT ip), SUM(price) 
  FROM tablename 
 WHERE time >= :yesterday 
   AND time < :today GROUP BY name

Upvotes: 63

user2463590
user2463590

Reputation: 21

Try the following:

SELECT DISTINCT(ip), name, COUNT(name) nameCnt, 
time, price, SUM(price) priceSum
FROM tablename 
WHERE time >= $yesterday AND time <$today 
GROUP BY ip, name

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838416

Replacing FROM tablename with FROM (SELECT DISTINCT * FROM tablename) should give you the result you want (ignoring duplicated rows) for example:

SELECT name, COUNT(*)
FROM (SELECT DISTINCT * FROM Table1) AS T1
GROUP BY name

Result for your test data:

dave 2
mark 2

Upvotes: 13

Andy
Andy

Reputation: 17771

You can just add the DISTINCT(ip), but it has to come at the start of the query. Be sure to escape PHP variables that go into the SQL string.

SELECT DISTINCT(ip), name, COUNT(name) nameCnt, 
time, price, SUM(price) priceSum
FROM tablename 
WHERE time >= $yesterday AND time <$today 
GROUP BY ip, name

Upvotes: 4

lexu
lexu

Reputation: 8849

Somehow your requirement sounds a bit contradictory ..

group by name (which is basically a distinct on name plus readiness to aggregate) and then a distinct on IP

What do you think should happen if two people (names) worked from the same IP within the time period specified?

Did you try this?

SELECT name, COUNT(name), time, price, ip, SUM(price) 
  FROM tablename 
 WHERE time >= $yesterday AND time <$today 
GROUP BY name,ip

Upvotes: 2

Related Questions