Group by is not working as expected

Toad for Oracle
1

Hello,

We have noticed that the group by is not working as expected since its bringing back duplicate records … in this case we were expecting only 3 records instead of 6 records. I have tried both Union as well as Union all as a test and its bringing back the exact same result; can someone please have a look at the script below and advise on a possible solution? Thanks

Territory

Global Stat

Global OU

Amt.

CA

Y3170

AAZ

($957,260.10)

CA

Y3170

AAZ

$90,693.00

CA

Y3170

ACZ

($6,714.00)

CA

Y3170

ACZ

$22.50

CA

Y3170

ARZ

($148,460.20)

CA

Y3170

ARZ

$7,666.00

select rpad(’"’||‘CA’||’"’||’,’||’"’||substr(O124951.TERRITORY,1,2)||’"’||’,’||’"’||substr(O124951.GLOBAL_STAT_ACCT,1,5)

||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘03’ ||’"’||’,’||’"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||

to_char (sum (ENTERED_YEAR_TO_DATE_NUM * -1)),80, ’ ') AMT

FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951

WHERE

( O124951.CURRENCY_CODE = ‘STAT’ ) AND ( O124951.ACTUAL_FLAG = ‘A’ ) AND

(O124951.ENTERED_YEAR_TO_DATE_NUM <> 0.00) AND

( O124951.SEGMENT3 in (‘91101’,‘91201’,‘91301’,‘93201’,‘93211’,‘93202’,‘93212’,‘93208’,‘91102’,‘91103’,‘91104’,‘91105’,‘91106’,‘91107’,‘91108’,

‘91322’,‘91323’,‘91324’,‘91325’,‘91326’,‘91327’,‘91328’,‘91329’,‘91330’,‘91331’,‘91332’,‘91333’,‘91334’,‘91335’,‘91336’,‘91337’,‘93203’,‘93204’,‘93205’) )

AND ( O124951.PERIOD_NAME = ‘Sep-15’ )

group by substr(O124951.TERRITORY,1,2) ,substr(O124951.GLOBAL_STAT_ACCT,1,5),substr(O124951.GLOBAL_OU,1,3)

UNION ALL

select rpad(’"’||‘CA’||’"’||’,’||’"’||substr(O124951.TERRITORY,1,2)||’"’||’,’||’"’||substr(O124951.GLOBAL_STAT_ACCT,1,5)

||’"’||’,’||’"’||‘2016’||’"’||’,’||’"’||‘03’ ||’"’||’,’||’"’||substr(O124951.GLOBAL_OU,1,3)||’"’||’,’||

to_char (sum (ENTERED_YEAR_TO_DATE_NUM)),80, ’ ') AMT

FROM APPS.IPR_PWC_ACCT_INQ_SUMMARY O124951

WHERE

( O124951.CURRENCY_CODE = ‘STAT’ ) AND ( O124951.ACTUAL_FLAG = ‘A’ ) AND

(O124951.ENTERED_YEAR_TO_DATE_NUM <> 0.00) AND

( O124951.SEGMENT3 in (‘91501’,‘91502’,‘91503’,‘91504’,‘91505’,‘91506’,‘91507’,‘91508’,‘91509’,‘91510’,‘91511’,‘91512’,‘91513’,‘91514’,‘91537’) )

AND ( O124951.PERIOD_NAME = ‘Sep-15’ )

group by substr(O124951.TERRITORY,1,2) ,substr(O124951.GLOBAL_STAT_ACCT,1,5),substr(O124951.GLOBAL_OU,1,3)

2

Is there some reason why this can’t be one query, rather than 2 separate queries joined by a UNION ALL? I’m thinking that you are getting 3 records from each part.

If you can’t separate them, then you could take out all the RPAD stuff and leave the 4 fields separate in select clauses, then

WITH DATA as

(your query above)

select

territory, global_stat, global_ou, sum(amount) as amount

from data

group by territory, global_stat, global_ou;

this of course assumes that you want the value of the like rows summed. You could add the RPAD stuff back in the new part of the query if you want.

3

Thanks John…we are using union in this case since one query is taking the YTD value and * -1 and the other is just taking the YTD. I will try your suggestion to see if this works

4

I see.

Here’s an example of how you could multiple a value by one thing or another, depending on a value in a different column. I think this technique might help you simplify your query.

select ename, job,

sal * (case

when JOB = ‘SALESMAN’ then -1

else 1

end) salary

from scott.emp