Question 267120 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – inner queries with outer group by column

I have this table called s2s_actions:

    id  brand_id  event_type
------  --------  ----------
    22       361           0
    29       361           1
    35         7           1
    40         7           0
    41        37           1
    42        37           0
    43        37           1

and I want to get the count of event_type=0 and count of event_type=1 for each brand_id:

SELECT s2s_actions.brand_id, deposits.deposit_amount, signups.signup_amount 
FROM 
s2s_actions,
(SELECT COUNT(*) AS signup_amount FROM s2s_actions WHERE brand_id=s2s_actions.brand_id AND event_type=0) signups,
(SELECT COUNT(*) AS deposit_amount FROM s2s_actions WHERE brand_id=s2s_actions.brand_id AND event_type=1) deposits
GROUP BY brand_id

If I put a number in the inner selects (like 37), this works ok, but I can’t use the brand_id of the outer group…

Any ideas?

Tags:

2

Answers


  1. 0 
    SELECT s2s_actions.brand_id, signups.signup_amount, deposits.deposit_amount
     FROM s2s_actions
LEFT JOIN (
  SELECT brand_id, COUNT(*) AS signup_amount
     FROM s2s_actions
     WHERE event_type = 0
     GROUP BY brand_id
) signups 
     ON s2s_actions.brand_id = signups.brand_id
LEFT JOIN (
  SELECT brand_id, COUNT(*) AS deposit_amount
     FROM s2s_actions
     WHERE event_type = 1
     GROUP BY brand_id
) deposits 
     ON s2s_actions.brand_id = deposits.brand_id
     GROUP BY s2s_actions.brand_id;

    The subqueries calculate the counts for event_type=0 and event_type=1 separately, grouping them by the brand_id.

    The main query joins these subqueries using the brand_id as the common column.

    Login or Signup to reply.
  2. 0

    This is using conditional aggregation, which is a great option for your particular need.

    select 
 brand_id, 
 sum(case when event_type = 0 then 1 end) as signups, 
 sum(case when event_type = 1 then 1 end) as deposits 
from s2s_actions
group by brand_id
    brand_id signups deposits
    361 1 1
    7 1 1
    37 1 2

    And if you have brand id’s without those targeted events and want to display zero rather than null in your results, then coalesce those two rows of code…

     coalesce(sum(case when event_type = 0 then 1 end), 0) as signups, 
 coalesce(sum(case when event_type = 1 then 1 end), 0) as deposits
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search