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

Mysql – group by and count from another table

I have the following tables:

s2s_actions

    id  app_id  brand_id  country_id
------  ------  --------  ----------
    22      15       361           5
    29      15       361           5
    35      15         7          64
    40      15         7          64
    41      15        37           5
    44      15       361           5

clicks

    id  app_id  country_id  brand_id 
------  ------  ----------  -------- 
   103      15           5       361 
   104      15          64         7 
   105      15           5        37 
   106      15           5        37

Is it possible to have a query that groups s2s_actions by brand_id,country_id,app_id and counts the number of clicks for each group?

in that case, results should be:

app_id  country_id  brand_id  total_clicks
------  ----------  --------  ------------
    15           5       361             1
    15          64         7             1
    15           5        37             2

Thanks!

Tags:

2

Answers


  1. 0

    You can use a simple group by followed by a correlated sub-query:

    select app_id, country_id, brand_id, (
  select count(*)
  from clicks
  where app_id = s2s_actions.app_id
  and country_id = s2s_actions.country_id
  and brand_id = s2s_actions.brand_id
) as total_clicks
from s2s_actions
group by app_id, country_id, brand_id
    Login or Signup to reply.
  2. 0

    This is an option using inner join :

    select s.app_id, s.country_id, s.brand_id, count(*) as total_clicks
from (
  select distinct s.app_id, s.country_id, s.brand_id, c.id
  from s2s_actions s
  inner join clicks c on c.app_id = s.app_id and c.country_id = s.country_id and c.brand_id = s.brand_id
) as s
group by s.app_id, s.country_id, s.brand_id;

    Results :

    app_id  country_id  brand_id    total_clicks
15      5           361         1
15      64          7           1
15      5           37          2

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search