Getting total number of counts for distinct entrys for the same id

Posted on

Question :

I have a table which is 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. How can I do that?

Sample 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
(34, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(35, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(36, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(37, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 50, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0);

I am expecting the result like this.

ad_id  Impression views click
49       1         0      1
50       0         0      1

Answer :

First you need to group by ad_id and then group by visitor_ip
then select max for impression, view and click

so your query would look like

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

NOTE : You should avoid using reserved keywords as column name.

Leave a Reply

Your email address will not be published.