0

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
3
  • IMHO you should add sample data and expected result, because it's unclear what you are asking.
    – DEarTh
    Apr 18 '18 at 5:08
  • The (90) means nothing on INT. INT is a 4-byte signed number -2 billion to +2 billion, regardless of that number.
    – Rick James
    May 4 '18 at 23:02
  • No PRIMARY KEY??
    – Rick James
    May 4 '18 at 23:03
1

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.

9
  • if I want to show it to a particular user what can I do for example this query is showing error SELECT id, ad_id,MAX(impression), MAX(view), MAX(clicks) FROM ad_analytics GROUP BY ad_id,visitor_ip WHERE publisher_id= 49;
    – arunwebber
    Apr 18 '18 at 9:18
  • Just add a where condition like where ad_id = 1
    – DEarTh
    Apr 18 '18 at 9:22
  • SELECT id, ad_id,MAX(impression), MAX(view), MAX(clicks) FROM ad_analytics WHERE publisher_id =10 GROUP BY ad_id,visitor_ip; This worked
    – arunwebber
    Apr 18 '18 at 9:25
  • please conside this question as well. dba.stackexchange.com/questions/205429/…
    – arunwebber
    May 1 '18 at 6:40
  • @JitendraSoftgrid - Don't include id in the SELECT; it is non-deterministic. Do in clude visitor_ip.
    – Rick James
    May 4 '18 at 23:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.