Combining two queries when using a group by

Question

Having some trouble figuring out the logic behind this. See Two Queries below:

Request 1:

SELECT cId, crId, COUNT(EventType) FROM Data WHERE EventType='0' OR EventType='0p' OR EventType='n' OR EventType = 'np' GROUP BY crId; 

Request 2:

 SELECT cId, crId, COUNT(EventType) AS Clicks FROM Data WHERE EventType='c' GROUP BY crId; 

It was just interesting if there was a way to make the column that I get at the end of query 2 appear in query 1. Since the where statements are different, they’re not quite sure where to go, and any subquery that ve wrote just didn't work.

Thank you in advance

Answer 1

 SELECT cId, crId, SUM(CASE WHEN EventType='0' OR EventType='0p' OR EventType='n' OR EventType = 'np' THEN 1 ELSE 0 END) AS Count_1, SUM(CASE WHEN EventType='c' THEN 1 ELSE 0 END) AS Count_2 FROM Data WHERE EventType IN ('0','0p','n','np','c') GROUP BY crId; 

Answer 2

You can join the two using the second as a correlated subquery.

 SELECT Data.cId, Data.crId, COUNT(EventType) AS event_type_count, click_counts.Clicks FROM Data /* Correlated subquery retrieves the Clicks (EventType 'c') per cId */ LEFT JOIN ( SELECT cId, crId, COUNT(EventType) AS Clicks FROM Data WHERE EventType='c' GROUP BY crId ) AS click_count ON Data.cId = click_count.cId AND Data.crId = click_count.crId /* OR chain replaced with IN() clause */ WHERE Data.EventType IN ('0','0p','n','np') /* This GROUP BY should probably also include Data.cId... */ GROUP BY Data.crId; 

Answer 3

You can do this all the queries from the table once and with the help of CASE statements.

 SELECT cId, crId, SUM(CASE WHEN EventType IN ('0', '0p', 'n', 'np') THEN 1 ELSE 0 END) as events, SUM(CASE WHEN EventType = 'c' THEN 1 ELSE 0 END) as clicks FROM Data WHERE EventType IN ('0', '0p', 'n', 'np', 'c') GROUP BY crId; 

Answer 4

Do you want to use IN ?

 SELECT cId, crId, COUNT(EventType) as Clicks FROM Data WHERE EventType IN ('0','0p','n','np','c') GROUP BY crId; 

---

:) Trying in the right direction;)

sqlfiddle demo

 select id, crid, count(case when type <> 'c' then crid end) count_others, count(case when type ='c' then crid end) count_c from tb group by crid ;