Mysql从stats数据库中获取多个计数

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

You can use subquery to achieve this:

SELECT
  t.uid,
  SUM(t.count) AS count,
  GROUP_CONCAT(CONCAT(t.country, ' => ', t.views) SEPARATOR ', ') AS country,
  SUM(t.sum) as sum,
  t.date
FROM (
  SELECT
    s.uid,
    COUNT(DISTINCT s.ip) AS count,
    s.country,
    COUNT(s.country) as views,
    SUM(s.rate)AS sum,
    s.date
  FROM stats s
  GROUP BY uid, date, country
  ) AS t
GROUP BY
t.uid,
t.date

Also available at sqlfiddle.

SUM needs a column and you gave string 'rate' in it, remove the ' from rate column name try this,

SELECT 
   COUNT(DISTINCT `ip`)AS `count`, 
  `country`, 
  SUM(rate) AS `sum` 
  FROM `stats` 
  GROUP BY `uid`, `date`

You will have to add country into the GROUP condition too:

SELECT
    COUNT(DISTINCT `ip`) AS `count`,
    `country`,
    COUNT(`country`) as `countryViewsByUser`,   -- added
    SUM(`rate`)AS `sum`
FROM
    `stats`
GROUP BY
    `uid`,
    `date`,
    `country`   -- added

You will just need to add country to your group by clause like below

$query="
SELECT 
COUNT(DISTINCT `ip`)AS `count`, 
`country`,
COUNT(DISTINCT `country`) AS country_count, 
SUM(`rate`) AS `sum` 
FROM `stats` 
GROUP BY `country`, `uid`, `date`
";

And please you need to move away from mysqli_* functions, and take a look at PDO instead