[SOLVED] My SQL - COUNT field on multitable group by other field

My SQL - COUNT field on multitable group by other field

I missed to create the correct request.

My request is :

select actionreal, sum(nb_actions) nb_actions from ( 
  select actionreal, count(distinct departement) nb_actions 
  from militant_action_nutrition 
  where actionreal in (1, 2, 3) 
  group by actionreal 
  union 
  select actionreal, count(distinct departement) nb_actions 
  from militant_action_jna 
  where actionreal in (1, 2, 3) 
  group by actionreal 
) t
group by actionreal

I need to get the number of distinct departement by actionreal on 2 tables.

In militant_action_nutrition I have
"Bas-Rhin" and "Manche" for actionreal=1 , "Bas-Rhin" et "Manche" for actionreal=2.

In militant_action_jna I have
"Bas-Rhin", "Manche" and "Yonne" for actionreal=1 , "Bas-Rhin" et "Manche" for actionreal=2.

My request result is :

1 | 5
2 | 2

But I need the result :

1 | 3
2 | 2

Thank you for help.

Solution

First do a union and then group by:

select actionreal, count(distinct departement) nb_actions from ( 
    select actionreal, departement
    from militant_action_nutrition 
    where actionreal in (1, 2, 3) 
    union 
    select actionreal, departement 
    from militant_action_jna 
    where actionreal in (1, 2, 3)
) t
group by actionreal

In all databases I know UNION operator will actually remove duplicate entries so the final count should be what you want.