CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
Developer
Developer

Reputation: 33

Max and min both in a Group BY

I have requirement where I need to pull Max of data when > 0 and Min of data when < 0. And the tricky part is it should be in a same column. Example as follows: Below Query:

SELECT A.employee_id, paycheck_number
      max ( CASE 
           WHEN B.special = 'XXT' THEN B.AMOUNT
           ELSE 0 
         END ) AMOUNT, 
       max ( CASE 
           WHEN B.special = 'XXH' THEN B.hrs1
           ELSE 0 
         END ) HRS1
FROM   Table1 A, 
       table2 B 
       LEFT OUTER JOIN table3 C 
                    ON B.company = C.company 
WHERE  A .employee_id = '123456789' 
GROUP  BY A.employee_id, paycheck_number 
ORDER  BY 1

Returns:

EMPLOYEE_ID   AMOUNT     HRS1     paycheck_number
123456789     2799.82    134.84   1234
123456789     832.86     40       4321
123456789     0          0        5678

If removed the group by the data is :

EMPLOYEE_ID   AMOUNT     HRS1     paycheck_number
123456789     0          134.84   1234
123456789     2799.82    0        1234
123456789     0          40       4321
123456789     832.86     0        4321
123456789     0          -40      5678
123456789     -832.86    0        5678

Whereas I want:

EMPLOYEE_ID   AMOUNT     HRS1     paycheck_number
123456789     2799.82    134.84   1234
123456789     832.86     40       4321
123456789     -832.86    -40      5678

It looks simple but when I try It doesn't work.

Upvotes: 1

Views: 84

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

In Oracle you get the value for the maximum absolute amount via KEEP DENSE_RANK FIRST/LAST:

max(value) keep (dense_rank last order by abs(value))

However, when there is always only the one non-zero value in one record and zeros or nulls in the other records to consider, you could simply add them:

sum(value)

In your case where only one record actually contains a value, you are creating the zero entries yourself and thus get 0 instead of, say, -40 when asking for the maximum value. Remove the ELSE branches that create the interfering zeros, and MAX will get you the correct value. E.g.:

max(case when b.special = 'XXT' then b.amount end) as amount

Upvotes: 1

Related Questions