All Forums Database
nhoigal 5 posts Joined 05/15
06 May 2015
Help with query- I receive error 3504

Hello,
 
I'm new to teradata, and I'm having problems with the following query. I receive the following error:
SELECT Failed.  [3504] Selected non-aggregate values must be part of the associated group.

SELECT 	
   TRUNC (CAST (created_at AS DATE), 'IW') AS first_day_week	
   ,USER_ACCESS_mode	
   ,traffic_source	
   ,d.segment_supply_view	
   ,d.main_Category	
   ,COUNT (DISTINCT user_id ) AS unique_buyers	
   ,SUM (gross_booking * cur_to_dollar_rate_at_creation) AS gb_usd	
   ,SUM (gross_revenue * cur_to_dollar_rate_at_creation) AS gr_usd	
FROM dwh_mart_view.v_billings_ini_successful AS b 	
JOIN emea_analytics.eu_deals_dim AS d ON d.country_id = b.country_id AND d.deal_id = b.deal_id 	
LEFT JOIN (	
   SELECT	
      a.country_key, a.order_id,traffic_source	
   FROM dwh_mart_view.attr_1_orders AS a	
   LEFT JOIN dwh_mart_view.ref_attr_class ac  ON a.ref_attr_class_key = ac.ref_attr_class_key
   WHERE attribution_type = 'v3' 	
   GROUP BY 1,2,3  
) AS c ON c.country_key = b.country_id AND c.order_id = b.billing_id	

I would really appriciate any help you can offer :)
 
Best Regards,
Ilana

dnoeth 4628 posts Joined 11/04
06 May 2015

Hi Ilana,
you need to add all columns without aggregation function to GROUP BY:

SELECT 
   TRUNC (CAST (created_at AS DATE), 'IW') AS first_day_week    
   ,USER_ACCESS_mode    
   ,traffic_source  
   ,d.segment_supply_view   
   ,d.main_Category 
   ,COUNT (DISTINCT user_id ) AS unique_buyers  
   ,SUM (gross_booking * cur_to_dollar_rate_at_creation) AS gb_usd  
   ,SUM (gross_revenue * cur_to_dollar_rate_at_creation) AS gr_usd  
FROM dwh_mart_view.v_billings_ini_successful AS b   
JOIN emea_analytics.eu_deals_dim AS d ON d.country_id = b.country_id AND d.deal_id = b.deal_id  
LEFT JOIN ( 
   SELECT  
      a.country_key, a.order_id,traffic_source  
   FROM dwh_mart_view.attr_1_orders AS a    
   LEFT JOIN dwh_mart_view.ref_attr_class ac  ON a.ref_attr_class_key = ac.ref_attr_class_key
   WHERE attribution_type = 'v3'   
   GROUP BY 1,2,3  
) AS c ON c.country_key = b.country_id AND c.order_id = b.billing_id    
GROUP BY 1,2,3,4,5

 

Dieter

nhoigal 5 posts Joined 05/15
07 May 2015

Thank you :-)

You must sign in to leave a comment.