dtio35880438
2016-08-25 07:07
浏览 89
已采纳

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

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • douju3911 2016-08-25 07:42
    已采纳

    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.

    已采纳该答案
    打赏 评论
  • douliedai4838 2016-08-25 07:14

    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`
    
    打赏 评论
  • duanlujiaji10335 2016-08-25 07:20

    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
    
    打赏 评论
  • down2323 2016-08-25 07:24

    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

    打赏 评论

相关推荐 更多相似问题