CODEWITHSUNDEEP
phpmysql
user1411607
user1411607

Reputation:

Mysql get multiple counts from stats database

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

Upvotes: 0

Views: 65

Answers (4)

Andrew
Andrew

Reputation: 1866

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.

Upvotes: 1

kennasoft
kennasoft

Reputation: 1593

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

Upvotes: 0

Shaharyar
Shaharyar

Reputation: 12449

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

Upvotes: 1

Rohan Kumar
Rohan Kumar

Reputation: 40639

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`

Upvotes: 1

Related Questions