I need to get unique counts along with country counts and sum rate for every user
I have come up with this basic design for database where uid
is user id
DROP TABLE IF EXISTS `stats`;
CREATE TABLE IF NOT EXISTS `stats` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` int(5) UNSIGNED NOT NULL,
`country` int(3) UNSIGNED NOT NULL,
`ip` int(10) UNSIGNED NOT NULL,
`date` int(10) UNSIGNED NOT NULL,
`timestamp` int(10) UNSIGNED NOT NULL,
`rate` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `stats`
(`id`, `uid`, `country`, `ip`, `date`, `timestamp`, `rate`) VALUES
(1, 1, 10, 1111111111, 2222222222, 3333333333, 100),
(2, 1, 10, 1111111112, 2222222222, 3333333333, 100),
(3, 2, 10, 1111111111, 2222222222, 3333333333, 100),
(4, 1, 10, 1111111114, 2222222223, 3333333333, 100),
(5, 1, 11, 1111111112, 2222222223, 3333333333, 100),
(6, 1, 10, 1111111111, 2222222223, 3333333333, 100);
And this is the query I am using to fetch daily counts
$query="
SELECT `uid`,
COUNT(DISTINCT `ip`)AS `count`,
`country`,
SUM(`rate`) AS `sum`,
`date`
FROM `stats`
GROUP BY `uid`, `date`
";
$result=mysqli_query($connection, $query) or trigger_error(mysqli_error($connection), E_USER_ERROR);
while($row = mysqli_fetch_assoc($result)){
echo 'userid:'.$row['uid'].' count:'.$row['count'].' country:'.$row['country'].' sum:'.$row['sum'].' date:'.$row['date'].'<br>';
};
I am getting this result
userid:1 count:2 country:10 sum:200 date:2222222222
userid:1 count:3 country:10 sum:300 date:2222222223
userid:2 count:1 country:10 sum:100 date:2222222222
Expected result
userid:1 count:2 country:10=>2 sum:200 date:2222222222
userid:1 count:3 country:10=>2, 11=>1 sum:300 date:2222222223
userid:2 count:1 country:10=>1 sum:100 date:2222222222
I guess I need something like SELECT DISTINCT country FROM stats
to get country counts in main query.
Please see and suggest any possible way to do this.
Thanks