General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Wednesday

Wednesday

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Wednesday

Wednesday

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

Highlighted
##
##### Re: Help with grouping

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Wednesday

Wednesday

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Wednesday

Wednesday

Hi Dieter.

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

Cheers.

Carlos.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Thursday

Thursday

Well, great minds think alike ;-)