Select Multiple Counts from multiple JOIN table that has multiple Group_BYs

Question

I have the following SQL getting correct result:

SELECT 
companies.name as company_name, 
locations.country_code as location,
ad_messages.ad_template_name as creative, 
impressions.width as width,
impressions.height as height, 
count('impressions.id') as impressions 
FROM "impressions" 
INNER JOIN "ad_messages" ON "ad_messages"."id" = "impressions"."ad_message_id" 
INNER JOIN "visitors" ON "visitors"."id" = "ad_messages"."visitor_id" 
INNER JOIN "companies" ON "companies"."visitor_id" = "visitors"."id" 
INNER JOIN "locations" ON "locations"."visitor_id" = "visitors"."id" 
WHERE (visitors.user_id = 171) 
 AND (impressions.created_at >= '2016-03-17 16:17:20.241276' 
 AND impressions.created_at <= '2016-05-16 16:17:20.241336') 
 GROUP BY 
 companies.name, 
 locations.country_code,
 ad_messages.ad_template_name, 
 impressions.width, 
 impressions.height 
 ORDER BY 
 companies.name, 
 locations.country_code,
 ad_messages.ad_template_name, 
 impressions.width, 
 impressions.height

I get the result (count of impressions) grouped and subgrouped the company_name, locations and ad_messages.

Impressions only Result

I can repeat the process for clicks and get result ( count of clicks ) grouped and subgrouped by different filters like so:

SELECT 
companies.name as company_name, 
locations.country_code as location,
ad_messages.ad_template_name as creative, 
ad_clicks.width as width,
ad_clicks.height as height, 
count('ad_clicks.id') as ad_clicks
FROM "ad_messages" 
INNER JOIN "ad_clicks" ON "ad_clicks"."ad_message_id" = "ad_messages"."id"
INNER JOIN "visitors" ON "visitors"."id" = "ad_messages"."visitor_id" 
INNER JOIN "companies" ON "companies"."visitor_id" = "visitors"."id" 
INNER JOIN "locations" ON "locations"."visitor_id" = "visitors"."id"
WHERE (visitors.user_id = 171) 
 AND (ad_clicks.created_at >= '2016-03-17 16:17:20.241276' 
 AND ad_clicks.created_at <= '2016-05-16 16:17:20.241336') 
 GROUP BY 
 companies.name, 
 locations.country_code,
 ad_messages.ad_template_name, 
 ad_clicks.width, 
 ad_clicks.height
  ORDER BY 
 companies.name, 
 locations.country_code,
 ad_messages.ad_template_name, 
 ad_clicks.width, 
 ad_clicks.height

However - I would like combine both impressions and clicks in one Query, I tried :

SELECT 
companies.name as company_name, 
locations.country_code as location,
ad_messages.ad_template_name as creative, 
impressions.width as width,
impressions.height as height, 
ad_clicks.width as click_width,
ad_clicks.height as click_height, 
count('impressions.id') as impressions,
count('ad_clicks.id') as clicks
FROM "ad_messages" 
INNER JOIN "impressions" ON "impressions"."ad_message_id" = "ad_messages"."id"
INNER JOIN "visitors" ON "visitors"."id" = "ad_messages"."visitor_id" 
INNER JOIN "companies" ON "companies"."visitor_id" = "visitors"."id" 
INNER JOIN "locations" ON "locations"."visitor_id" = "visitors"."id"
LEFT JOIN "ad_clicks" ON "ad_clicks"."ad_message_id" = "ad_messages"."id" 
WHERE (visitors.user_id = 171) 
 AND (impressions.created_at >= '2016-03-17 16:17:20.241276' 
 AND impressions.created_at <= '2016-05-16 16:17:20.241336') 
 GROUP BY 
 companies.name, 
 locations.country_code,
 ad_messages.ad_template_name, 
 impressions.width, 
 impressions.height,
 ad_clicks.width, 
 ad_clicks.height

This gave me totally wrong results! count(impression) and count(ad_clicks)had the same value. Is there any way I can get correct counts from one SQL?

Any help is appreciated. Thanks!

Pham X. Bach · Accepted Answer · 2016-05-17 00:22:36Z

You could use count(col_name) over (partition by .... order by ...) like this, use select distinct instead of group by. And try using alias instead of full_table_name.

SELECT DISTINCT
    companies.name as company_name, 
    locations.country_code as location,
    ad_messages.ad_template_name as creative, 
    impressions.width as width,
    impressions.height as height, 
    ad_clicks.width as click_width,
    ad_clicks.height as click_height, 
    count('impressions.id') over (partition by companies.name, locations.country_code, 
            ad_messages.ad_template_name, impressions.width, impressions.height 
            order by    companies.name, locations.country_code, ad_messages.ad_template_name, 
            impressions.width, impressions.height)  as impressions,
    count('ad_clicks.id') over (partition by companies.name, locations.country_code, 
            ad_messages.ad_template_name, ad_clicks.width, ad_clicks.height 
            order by companies.name, locations.country_code, ad_messages.ad_template_name, 
            ad_clicks.width, ad_clicks.height ) as clicks
FROM "ad_messages" 
INNER JOIN "impressions" ON "impressions"."ad_message_id" = "ad_messages"."id"
INNER JOIN "visitors" ON "visitors"."id" = "ad_messages"."visitor_id" 
INNER JOIN "companies" ON "companies"."visitor_id" = "visitors"."id" 
INNER JOIN "locations" ON "locations"."visitor_id" = "visitors"."id"
LEFT JOIN "ad_clicks" ON "ad_clicks"."ad_message_id" = "ad_messages"."id" 
WHERE (visitors.user_id = 171) 
     AND (impressions.created_at >= '2016-03-17 16:17:20.241276' 
     AND impressions.created_at <= '2016-05-16 16:17:20.241336') 
ORDER BY 
    companies.name, 
    locations.country_code,
    ad_messages.ad_template_name, 
    impressions.width, 
    impressions.height,
    ad_clicks.width, 
    ad_clicks.height

A test in SQLFIDDLE

Collectives™ on Stack Overflow

Select Multiple Counts from multiple JOIN table that has multiple Group_BYs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related