CODEWITHSUNDEEP
sql-server
semiflex
semiflex

Reputation: 1246

How do I Group By and Count a column with 2 values at the same time?

I have a table called campaign. This is what it looks like: 

website event type
amazon  imp
amazon  imp
amazon  imp
amazon  click
apple   click
apple   imp
adidas  click
adidas  imp
adidas  click
adidas  imp

I want to group it by the different values in the event type column and then count the different values in event type. My table should end up looking like this: 

website imp click
amazon  3   1
apple   1   1
adidas  2   2

How would I go about doing this?

EDIT 14/03/19 :

Where I got to so far : 

  SELECT a.[webite], case when a.[event_type] = 'imp' then 1 end as 'imp', case when a.[event_type] = 'click' then 1 end as 'click'
  FROM [campaign] as a
  INNER JOIN [campaign] as b 
  ON a.[webite] = b.[webite]

Upvotes: 2

Views: 33

Answers (3)

Suraj Kumar
Suraj Kumar

Reputation: 5643

Out of the available other option as answered by another user you can also try the below query.

select a.website, imp, click from(
     select website, count(eventtype) as imp
     from test a where a.eventtype = 'imp' group by website
)a 
inner join (
     select website, count(eventtype) as click
     from test b where b.eventtype = 'click' group by website  
)b on a.website = b.website

Demo

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use PIVOT with COUNT

DECLARE @SampleTable TABLE (website VARCHAR(10), [event type]  VARCHAR(10))
INSERT INTO @SampleTable VALUES
('amazon' ,'imp'),
('amazon' ,'imp'),
('amazon' ,'imp'),
('amazon' ,'click'),
('apple' ,'click'),
('apple' ,'imp'),
('adidas' ,'click'),
('adidas' ,'imp'),
('adidas' ,'click'),
('adidas' ,'imp')


SELECT * FROM @SampleTable
PIVOT (COUNT([event type]) FOR [event type] IN ([imp], [click])) PVT

Result:

website    imp         click
---------- ----------- -----------
adidas     2           2
amazon     3           1
apple      1           1

Upvotes: 4

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want conditional aggregation :

select website,
       sum(case when event = 'imp' then 1 else 0 end) as imp,
       sum(case when event = 'click' then 1 else 0 end) as click
from campaign c
group by website;

Upvotes: 2

Related Questions