Checking using nvl2 with multiple group by

Question

I have a table like

------------------------
S.No Name Amount Imp_Num
1     A    10     12345
2     B    20             
3     A    30      
4     C    40     4555
5     B    50
--------------------------

and I want something like

---------------------------------------
Name Total_Amount  Imp_Num   Imp_Num_Present
 A      40          12345      Y
 B      70          null       N
 C      40          4555       Y
---------------------------------------

The important_number_present column should be Y if the important number is present for the particular name at least once and the important number should be captured. The important number for a particular name is assumed to be the same.If different the latest one should be displayed as imp_numb. (But this is of secondary priority).

I tried something like

Select sum(amount) as total_amount, imp_num, nvl2(imp_num,'Y','N') from sampletable group by imp_num;

But name can't be retrieved and the data doesn't make sense without the name. I might be doing something totally wrong. Can a feasible solution be done in SQL rather than in pl/sql.

Group by with name is returning the name with a null entry and imp_num entry.

I am cracking my head on this. Would be of great help, if someone solves it.

Thanks in advance

ScaisEdge · Accepted Answer · 2018-03-07T17:28:20.243

2

You could use a (fake) aggregation function on imp_num and group by name

  Select Name, sum(amount) as total_amount, max(imp_num), nvl2( max(imp_num),'Y','N') 
  from sampletable 
  group by Name;

edited Mar 07 '18 at 17:28

answered Mar 07 '18 at 12:06

ScaisEdge

131,976
10
91
107

Perfect! Thank you so much :-) – Pooja smithi Mar 07 '18 at 17:06
Is there a way to do it without using max()? In pl/SQL? – Pooja smithi Mar 08 '18 at 02:28
Yes if you use a procedural approach you can avoid the max() function .. you shoul iterate over the list of distinct name for retrive the related value and then check for the the presence of the field ...but is another question.. – ScaisEdge Mar 08 '18 at 06:39

Kaushik Nayak · Answer 2 · 2018-03-08T04:55:03.410

EDIT: Another solution with COUNT function. DEMO

SELECT name
    ,SUM(amount) AS total_amount
    ,MAX(imp_num) AS Imp_Num
    ,CASE 
        WHEN Count(imp_num) > 0
            THEN 'Y'
        ELSE 'N'
        END AS Imp_Num_Present
FROM yourtable
GROUP BY name

You may also use a MAX( CASE ) block

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE yourtable
    (S_No int, Name varchar2(1), Amount int, Imp_Num varchar2(5))
;

INSERT ALL 
    INTO yourtable (S_No, Name, Amount, Imp_Num)
         VALUES (1, 'A', 10, '12345')
    INTO yourtable (S_No, Name, Amount, Imp_Num)
         VALUES (2, 'B', 20, NULL)
    INTO yourtable (S_No, Name, Amount, Imp_Num)
         VALUES (3, 'A', 30, NULL)
    INTO yourtable (S_No, Name, Amount, Imp_Num)
         VALUES (4, 'C', 40, '4555')
    INTO yourtable (S_No, Name, Amount, Imp_Num)
         VALUES (5, 'B', 50, NULL)
SELECT * FROM dual
;

Query 1:

  SELECT Name,
         SUM (amount) AS total_amount,
         MAX (imp_num) AS Imp_Num,
         CASE
            WHEN MAX (CASE WHEN imp_num IS NOT NULL THEN 1 ELSE 0 END) = 1
            THEN
               'Y'
            ELSE
               'N'
         END
            AS Imp_Num_Present
    FROM yourtable
GROUP BY Name

Results:

| NAME | TOTAL_AMOUNT | IMP_NUM | IMP_NUM_PRESENT |
|------|--------------|---------|-----------------|
|    A |           40 |   12345 |               Y |
|    B |           70 |  (null) |               N |
|    C |           40 |    4555 |               Y |

@Pooja.chek another solution with count function. It is a bit efficient than using MAX, since there wont be sorting. Moreover, `NVL2` being specific to oracle might not be portable with other DBMS, but case and count is standard SQL. — Kaushik Nayak, Mar 08 '18 at 05:49

Checking using nvl2 with multiple group by

2 Answers2