CODEWITHSUNDEEP
phpmysql
user1411607
user1411607

Reputation:

MySQL GROUP BY returns one row

I need to get total no of unique rows on daily ip bases, Repeating ip on another day shall be counted for row.

I also followed these link, link, approach's but didn't worked

I am doing this way

Database

|    ip      |  date    |
| 1698170033 | 19032016 |
| 1698170034 | 19032016 |
| 1698170033 | 19032016 |
| 1698170033 | 20032016 |
| 1698170034 | 20032016 |
| 1698170035 | 20032016 |
| 1698170036 | 20032016 |
| 1698170033 | 20032016 |

MySQl

SELECT 
`date`, `ip`
FROM `stats`.`stats`    
GROUP BY `date`

This gives result as

| 1698170033 | 19032016 |
| 1698170033 | 20032016 |

Expected result

| 1698170033 | 19032016 |
| 1698170033 | 20032016 |
| 1698170034 | 20032016 |
| 1698170035 | 20032016 |
| 1698170036 | 20032016 |

Please suggest any possible way to do this.

Thanks

Upvotes: 0

Views: 92

Answers (2)

Muhammad Arif
Muhammad Arif

Reputation: 1022

Try to use group by date and ip

SELECT `date`, `ip` FROM `stats`.`stats`GROUP BY `date`, `ip`

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need to GROUP BY ip field too:

SELECT `date`, `ip`
FROM `stats`.`stats`    
GROUP BY `date`, `ip`

This will give you a distinct list of all the date, ip pairs of your table.

Upvotes: 2

Related Questions