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  gt    yesterday     AND time  lt  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

User · Answer

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

User · Answer

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  gt    yesterday AND time  lt  today  GROUP BY ip  name

User · Answer

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

User · Answer

Try the following   SELECT DISTINCT ip   name  COUNT name  nameCnt   time  price  SUM price  priceSum FROM tablename  WHERE time  gt    yesterday AND time  lt  today  GROUP BY ip  name

User · Answer

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  gt    yesterday AND time  lt  today  GROUP BY name ip

[mysql] mysql query: SELECT DISTINCT column1, GROUP BY column2

Examples related to mysql

Examples related to select

Examples related to group-by

Examples related to distinct