mysql query: SELECT DISTINCT column1, GROUP BY column2

Question

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).

Solution 1

you can use COUNT(DISTINCT ip), this will only count distinct values

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Adam Tal]

I tried it, didn't get the right result. The reason I want to use DISTINCT on ip, is I don't want duplicate ip's. The reason I want to use GROUP BY on name is so I can count names (e.g. show one table row that tells me how many people with the name "mark" are there). I don't (and won't) have two names on the same IP in my db.

[Adam Tal]

Tried adding DISTINCT(ip) at the beginning, didn't work, it just ignores it. Where did the 'nameCnt' come from? am I writing the query wrong? (It worked with me w/o 'nameCnt' & 'priceSum' before, except for the DISTINCT thing)....

[Adam Tal]

Regarding GROUP BY name,ip ... It didn't solve the problem, I think it only groups by the first column and ignores the second.

[Andy]

It's better to alias the columns so you can refer to them by the alias in PHP rather than having to use $result['COUNT(name)'] - instead with alias $result['nameCnt']. I think you'll have to GROUP BY ip, name, answer updated

[lexu]

@Adam: It groups by name and IP .. you can see this more easily by moving the IP to the second position of the select list.

[Larry Lustig]

@Adam: If you don't have two names with the same IP, how come the first SELECT statement in your question includes duplicate IPs? Each ID should appear with one and only one name.

[Adam Tal]

@lexu not sure exactly what's going on over there, but it does not eliminate duplicates - not for names and not for ip's.

[Adam Tal]

@Larry Lustig - I did not mean each ip appears with only one name. I meant that if there are two duplicate ip's, they will always have the same name. What I'm trying to do is count that name just once for that ip (i.e. removing duplicate ip's).

[Adam Tal]

Sounds good. How do I extract the data though? (Never used SELECT within a SELECT before). Is Table1 my Table? What is T1? Thanks in advance

[Peter Lang]

@Adam: Yes, Table1 is the name of your table, while T1 is the alias for that sub-query, so you could also write SELECT T1.name, .... MySQL requires aliases for all sub-queries (+1 for this answer).

[Adam Tal]

Thanks Peter, and Mark. How do I use the data in my php though? I used $data = mysql_query("SELECT...") and then while($row = mysql_fetch_array($data)) { echo $row['name']; echo $row['COUNT(name)']; } But now it returns empty.

[Adam Tal]

OK, update: The problem now is that if I have a Dave using the same ip as Mark once, and then another Dave that doesn't use the same ip, this query won't show me any dave's (when it should still count the Daves that aren't sharing the same ip). Tried changing DISTINCT * to DISTINCT ip, but then I get an error.

[Adam Tal]

Oh hold on, wait a minute. Trying to reproduce the query to answer Marks question reveals: THIS DOES WORK!!!!! I can't believe it. I think I might cry.

[Ian Clelland]

You can't really do that -- distinct isn't a function; it's a keyword which applies to the entire row, or a modifier on an aggregate function. In your case, it's a keyword for SELECT, which is why it has to come before the column names. The parentheses you put around 'ip' are just decorative.

[micro]

How can we understand what you mean when you don't provide your entire SELECT statement?

[Ravinder Payal]

Are aggregate functions allowed with DISTINCT functions?