SELECT 
 u.entry_date, 
 u.affiliate_id, 
 u.country_name, 
 u.game_type, 
 u.game_category, 
 u.o_origin, 
 u.d_origin, 
 
 u.other_stages_sum,
 u.product_info_sum,
 u.confirmation_sum,
 u.purchase_agmt_sum,
 u.shipping_info_sum,
 u.thank_you_sum,
 u.billing_info_sum,
  
 ( SELECT d_game_downloads_agg.sum
   FROM d_game_downloads_agg
   WHERE d_game_downloads_agg.entry_date = u.entry_date AND d_game_downloads_agg.affiliate_id = u.affiliate_id AND d_game_downloads_agg.country_name::text = u.country_name::text AND d_game_downloads_agg.game_type::text = u.game_type::text AND d_game_downloads_agg.game_category::text = u.game_category::text AND d_game_downloads_agg.origin::text = u.d_origin::text
   ) AS total_downloads
   
   
   
FROM ( 
	SELECT go.entry_date, 
			go.affiliate_id, 
			go.country_name, 
			go.game_type, 
			go.game_category, 
			go.origin AS o_origin, 
			NULL::"unknown" AS d_origin,

			(select sum(sum) from d_game_orders_agg where 
stage not in ('Product Information','Confirmation','Purchase Agreement','Shipping Information','Thank you for your order!','Billing Information') and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as other_stages_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Product Information' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as product_info_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Confirmation' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as confirmation_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Purchase Agreement' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as purchase_agmt_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Shipping Information' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as shipping_info_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Thank you for your order!' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as thank_you_sum,

(select sum(sum) from d_game_orders_agg where 
stage='Billing Information' and
entry_date=go.entry_date and
affiliate_id=go.affiliate_id and
country_name=go.country_name and
game_type=go.game_type and
origin=go.origin and
--vendor_id=go.vendor_id and
game_category=go.game_category) as billing_info_sum

			
           FROM d_game_orders_agg go
           
           GROUP BY go.entry_date,go.affiliate_id,go.country_name,go.game_type,go.game_category,go.origin
	UNION
 	
	SELECT u.entry_date, 
			u.affiliate_id, 
			u.country_name, 
			u.game_type, 
			u.game_category, 
			NULL::"unknown" AS o_origin, 
			u.origin AS d_origin, 

			
			NULL as other_stages_sum,
			NULL as product_info_sum,
			NULL as confirmation_sum,
			NUlL as purchase_agmt_sum,
			NULL as shipping_info_sum,
			NULL as thank_you_sum,
			NULL as billing_info_sum
   FROM 
   			d_game_downloads_agg u
   ORDER BY 1) u
   
ORDER BY u.entry_date;