Query involving join - combine data from 2 of our tables

Question

We need to combine data from 2 of our tables - active_deals and deal_display_controls. The (relevant) columns in each of them are:

`active_deals` : `deal_id`, `region_code`
`deal_display_controls` : `deal_id`, `display_control_id`

The query needs to fetch all count of all the active deals having a given display_control_id and group them by their region_code (group by region code). Along with this, it also needs to group the active deals by region code (without the display_control_id constraint)

So, if the active deals tables looks like:

deal_id  region_code
   d1           US
   d2           CA
   d3           US   

And the deal_display_controls looks like:

`deal_id`    `display_control_id`
   d1              dc1
   d2              dc1
   d3              dc2
   d4              dc1

Then for a given display control id = “dc1” The query should output:

`region_code`    `count_of_active_deals_with_given_display_control_for_region_code   `count_of_active_deals_for_region_code`
  US                          1                                                                            2
  CA                          1                                                                            1

I could do this by splitting the above question into 2 parts and writing individual queries for each part: Part 1: To get the active deals for a given display_control_id and group by region code Query for the same:

select count(*), region_code from active_deals join deal_display_controls on active_deals.bin_deal_id=deal_display_controls.deal_id where deal_display_controls.display_control_id=0xc04e20724f5f49c9a285cb3c98d777b4 group by active_deals.region_code;

O/p:

`region_code`    `count_of_active_deals_with_given_display_control_for_region_code  
  US                          1                                                                                                                
  CA                          1     

Part2: To get the active deals and group by region code Query for the same:

select count(*), region_code from active_deals join deal_display_controls on active_deals.bin_deal_id=deal_display_controls.deal_id group by active_deals.region_code;

O/p:

`region_code`    `count_of_active_deals_for_region_code`
  US                          2
  CA                          1

I need to way to combine these 2 queries into a single query. Is it possible to do this?

Answer 1

while counting you can add additional condition of control_id

select d.region_code
     , count(distinct d.deal_id) count_of_active_deals_for_region_code
     , SUM(c.display_control_id = 'dc1') count_of_active_deals_with_given_display_control_for_region_code
  from active_deals d
  left 
  join deal_display_controls c
    on d.deal_id = c.deal_id
group 
    by d.region_code

Fiddle: http://sqlfiddle.com/#!9/904670/2