Home » SQL & PL/SQL » SQL & PL/SQL » Selecting the data greater than some value into one column
icon9.gif  Selecting the data greater than some value into one column [message #590116] Mon, 15 July 2013 00:27 Go to next message
i150825
Messages: 3
Registered: July 2013
Junior Member
[EDITED by LF: duplicate topic removed. Applied [code] tags.]



Hi Friends,


I have table TEST_REP with below data
DA                     SUMA                   
---------------------- ---------------------- 
2011                   2                      
2011                   3                      
2011                   5                      
2012                   2                      
2012                   7                      
2014                   2                      
2014                   10                     
2015                   2                      
2016                   33                     
2015                   26                     
2017                   21                     
2017                   2                      
2018                   23                     

13 rows selected 

I have used following query to get the below output:
 
select
         br_mat MAT_YEAR,
         sum(br_par)  TOTAL
from    (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
            select to_char(seeddate + level-1),0
                from dual d, (select min(da) min_maturity_year,
                              max(da) max_maturity_year,
                              TO_Number(TO_CHAR(sysdate,'YYYY')) seeddate
                              
                                                           from test_rep
                                ) t
                connect by level <= (t.max_maturity_year - t.min_maturity_year) + 1

           )
group by br_mat
order by br_mat;

Output :
 
MAT_YEAR                                 TOTAL                  
---------------------------------------- ---------------------- 
2011                                     10                     
2012                                     9                      
2013                                     0                      
2014                                     12                     
2015                                     28                     
2016                                     33                     
2017                                     23                     
2018                                     23                     
2019                                     0                      
2020                                     0                      

10 rows selected 


Expected Output :
MAT_YEAR                                 TOTAL                  

---------------------------------------- ---------------------- 
2011                                     10                     
2012                                     9                      
2013                                     0                      
2014                                     12                     
2015                                     28                     
2016                                     33          
2017 and Greater                         46


Please help .

[Updated on: Mon, 15 July 2013 03:23] by Moderator

Report message to a moderator

Re: Selecting the data greater than some value into one column [message #590117 is a reply to message #590116] Mon, 15 July 2013 00:33 Go to previous messageGo to next message
BlackSwan
Messages: 23191
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/m/590104/136107/#msg_590104

stop spamming this forum.
Re: Selecting the data greater than some value into one column [message #590118 is a reply to message #590117] Mon, 15 July 2013 00:36 Go to previous messageGo to next message
i150825
Messages: 3
Registered: July 2013
Junior Member
Sorry.. i am new to the forum.. and not able to delete my previos post..unknowgly happend..
Re: Selecting the data greater than some value into one column [message #590123 is a reply to message #590116] Mon, 15 July 2013 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 15 July 2013 07:06
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...



Regards
Michel

Re: Selecting the data greater than some value into one column [message #590124 is a reply to message #590118] Mon, 15 July 2013 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
i150825 wrote on Mon, 15 July 2013 07:36
Sorry.. i am new to the forum.. and not able to delete my previos post..unknowgly happend..


What does this mean? You don't want an answer to your topics?

Regards
Michel

Re: Selecting the data greater than some value into one column [message #590128 is a reply to message #590124] Mon, 15 July 2013 01:35 Go to previous messageGo to next message
i150825
Messages: 3
Registered: July 2013
Junior Member
Thanks fot the reply. sorry for my previous reply.I will follow the rules of the forum from now.Yes i need answer Michel.
Re: Selecting the data greater than some value into one column [message #590131 is a reply to message #590128] Mon, 15 July 2013 02:08 Go to previous message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So please, post what we asked (a test case and the specification of what you want the queries return).

Regards
Michel
Previous Topic: Query Problem
Next Topic: Regaring tablespace
Goto Forum:
  


Current Time: Sun Dec 28 12:05:52 CST 2014

Total time taken to generate the page: 0.10465 seconds