MySQL – Count Impressions and Clicks for Same Ad ID from Different IPs

MySQL

I have a table like this:

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to count the number of impressions, views, and clicks for a particular id for distinct IP Aka if there are four impressions from the same IP then it should be counted as one impression for a particular IP. Also if there is entry from two different ip for a same id then it should counted as 2?

Consider this data:

  INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`, `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`) VALUES
    (1, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (2, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (3, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (4, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (5, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (6, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0);

For this this query giving me.

SELECT id, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

This answer:

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 1, 0, 1),
(5, 49, 1, 0, 1);

But i am expecting answer like this:

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 2, 0, 2),

Also in resultant row I need to consider publisher_id as well. How can I do that?

Best Answer

I think the expected result you want can be fetched using this query.

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip) t group by t.ad_id;

UPDATE

Removed columns from select that are neither in group by nor aggregated, and added date_event

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  date_event
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     date_event
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip,date_event) t
group by t.ad_id,t.date_event;
Related Question