Help with grouping

General
CH
Fan

Help with grouping

Hello! I am getting stuck today with grouping. I get either a 3504 error if i group by 1,2,3 or a 3625 if i group by 1,2,3 plus any of the others no matter how i designate the group by.

SyntaxEditor Code Snippet

select 
distinct
namefirst,namelast,INDEXVALUE as "Profile ID",case when doctypecode = 'tkt' then sum (equivbasefareamt) else 0 end as total_base_fare_Amt,case when doctypecode = 'emd' then sum (equivbasefareamt) else 0 end as total_ancillary_amt,cast (total_base_fare_amt + total_ancillary_amt as integer) as  Total_Total 
from TABLES......
where.......
group by 1,2,3

 

4 REPLIES
Junior Contributor

Re: Help with grouping

You want conditional aggregation: a SUM over CASE

SELECT -- DISTINCT --not needed as DISTINCT already returns unique data
   namefirst,namelast,INDEXVALUE AS "Profile ID",
   Sum (CASE WHEN doctypecode = 'tkt' THEN equivbasefareamt ELSE 0 END) AS total_base_fare_Amt,
   Sum (CASE WHEN doctypecode = 'emd' THEN equivbasefareamt ELSE 0 end) AS total_ancillary_amt,
   Cast (total_base_fare_amt + total_ancillary_amt AS INTEGER) AS  Total_Total 
FROM TABLES......
WHERE.......
GROUP BY 1,2,3
Teradata Employee

Re: Help with grouping

Hi.

You may try something like:

 

select
-- distinct Not necessary
namefirst,
namelast,
INDEXVALUE as "Profile ID",
SUM(case when doctypecode = 'tkt' then equivbasefareamt else 0 end) as total_base_fare_Amt,
SUM(case when doctypecode = 'emd' then equivbasefareamt else 0 end) as total_ancillary_amt,
cast (total_base_fare_amt + total_ancillary_amt as integer) as Total_Total
from TABLES......
where.......
group by 1,2,3

 

Cheers.

 

Carlos.

Teradata Employee

Re: Help with grouping

Hi Dieter.

 

It seems we posted the same solution at the same time ;-)

 

Cheers.

 

Carlos.

Junior Contributor

Re: Help with grouping

Well, great minds think alike ;-)