Help with query- I receive error 3504

Database

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

2 REPLIES
N/A

Re: Help with query- I receive error 3504

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

Re: Help with query- I receive error 3504

Thank you :-)