CODEWITHSUNDEEP
mysqlsql
Jon Hagelin
Jon Hagelin

Reputation: 137

Combing two queries while using group by

Having some trouble figuring out the logic to this. See the two queries below:

Query 1:

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

Query 2:

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

Was just wondering if there was a way to make the column that I would get at the end of query 2 appear in query 1. Since the where statements are different, not really sure where to go, and any subquery that I've wrote just hasn't worked.

Thanks in advance

Upvotes: 4

Views: 92

Answers (4)

bonCodigo
bonCodigo

Reputation: 14361

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;

:) PUtting myself in 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
 ;

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270775

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;

Upvotes: 3

Tom
Tom

Reputation: 6663

You can do this all querying from the table once and using 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;

Upvotes: 2

fancyPants
fancyPants

Reputation: 51938

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;

Upvotes: 5

Related Questions