Checking using nvl2 with multiple group by

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

Solution

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;