I have two query to get count and sum of rate for unique ip's.
Query one groups by date and query two groups by country
This is the table
DROP TABLE IF EXISTS `stats`;
CREATE TABLE IF NOT EXISTS `stats` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(5) UNSIGNED NOT NULL,
`country` int(3) UNSIGNED NOT NULL,
`user_ip` int(50) UNSIGNED NOT NULL,
`timestamp` int(10) UNSIGNED NOT NULL,
`rate` int(7) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Dumping data for table `stats`
--
INSERT INTO `stats` (`id`, `user_id`, `country`, `user_ip`, `timestamp`, `rate`) VALUES
(1, 1, 1, 1111111111, 1489999983, 15000),
(2, 1, 2, 1111111112, 1489999984, 10000),
(3, 1, 1, 1111111111, 1489999985, 10000),
(4, 1, 1, 1111111111, 1490086333, 10000),
(5, 1, 2, 1111111111, 1490086334, 10000),
(6, 1, 1, 1111111121, 1490086335, 10000);
These are the queries I am using to get data
To get sum of rates based on date I use following query
SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`
FROM (
SELECT DISTINCT `user_ip`, `rate`, `timestamp`
FROM `stats`.`stats`
WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
) c
GROUP BY DATE(FROM_UNIXTIME(`timestamp`))
Result
date count rate
20-03-2017 2 25000
21-03-2017 2 20000
To get sum of rates based on country I use following query
SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`, `country`
FROM (
SELECT DISTINCT `user_ip`, `rate`, `timestamp`, `country`
FROM `stats`.`stats`
WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
) c
GROUP BY `country`
Result
country count rate
1 3 35000
2 1 10000
Since these two query are nearly same and fetches same rows from table is it possible to get both result from single query instead of two query.
Also please suggest if it can be be done in PHP effectively than MYSQL.
Thanks
Try this in php
$country="";
$groupByCondition="DATE(FROM_UNIXTIME(`timestamp`))";
if(/*BasedOnCountry*/){
$country=", `country`";
$groupByCondition = "`country`";
}
$query= "SELECT COUNT(`user_ip`) AS `count`, SUM(`rate`) AS `rate`, `timestamp`"+ $country +"
FROM (
SELECT DISTINCT `user_ip`, `rate`, `timestamp`"+ $country +"
FROM `stats`.`stats`
WHERE `user_id`=? `timestamp`>=? AND `timestamp`<=?
GROUP BY DATE(FROM_UNIXTIME(`timestamp`)),`user_ip`
) c
GROUP BY "+ $groupByCondition ;
//execute the query and get the results