Home » SQL & PL/SQL » SQL & PL/SQL » I don't believe if analytic functions do it for me or not (merged with follow-up question)
I don't believe if analytic functions do it for me or not (merged with follow-up question) [message #320437] Thu, 15 May 2008 02:25 Go to next message
zoroufi
Messages: 2
Registered: May 2008
Junior Member
Hey everyone,
I'm looking for a way handling this report for my own job.
a table having the following attributes exists.

Create table Test (
Public_Date varchar2(10),
City varchar2(10),
count number(3))

Query with the following output readily could be produced using group by clause.

Year Sum
---- ----
2005 23
2006 36
2007 15
2008 10

But the question is that How I can lead to the following output.
(I want to merge some records into one record in the output, in this example
sum of all years after 2005 is my interest not each year individually come before)

Year(s) Sum
---- ----
2005 23
2006 36
2007,2008 25 /*(15+10)*/

I think analytic functions may be useful in producing this output but I don't know how.
Could everyone help me how to handle this?
Re: I don't believe if analytic functions do it for me or not [message #320440 is a reply to message #320437] Thu, 15 May 2008 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd just use CASE and GROUP BY.

Something like
SELECT CASE WHEN year <= 2005 then to_char(year)
            ELSE  '2006 - 2008' END year
       ,sum(amount)
FROM   table
GROUP BY CASE WHEN year <= 2005 then to_char(year)
              ELSE  '2006 - 2008' END
How to handle this report? [message #325619 is a reply to message #320437] Fri, 06 June 2008 15:18 Go to previous messageGo to next message
zoroufi
Messages: 2
Registered: May 2008
Junior Member
Hey everyone,
I'm looking for a way handling this report for my own job.
a table having the following attributes exists.

Create table Test (
Public_Date varchar2(10),
City varchar2(10),
cnt number(3))
some records of this table are listed below:
Pub_date City cnt
------- --- ----
2005 a 10
2005 b 13
2006 a 21
2006 b 3
2006 c 2
2007 d 10
2007 a 8
2007 c 7
2008 b 9
2008 c 1

Query with the following output could be readily produced using group by clause.

Year Sum
---- ---
2005 23
2006 36
2007 15
2008 10

But the question is that How I can lead to the following output.
(I want to merge some records into one record in the output, in this example
sum of all years after 2005 is my interest not each year individually come before)

Year(s) Sum
------ ----
2005 23
2006 36
"after 2006" 25

Could everyone help me how to handle this?
Re: How to handle this report? [message #325620 is a reply to message #325619] Fri, 06 June 2008 15:23 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>Public_Date varchar2(10),
bad, bad, bad design.
Dates should always be held in DATE datatype

[Updated on: Fri, 06 June 2008 15:27] by Moderator

Report message to a moderator

Re: How to handle this report? [message #325621 is a reply to message #325619] Fri, 06 June 2008 15:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

http://www.psoug.org/reference/group_by.html

Regards
Michel
Re: How to handle this report? [message #325633 is a reply to message #325619] Fri, 06 June 2008 20:15 Go to previous message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
You should be able to adapt the previous solution that you were given. It should only require minimal modification.
Previous Topic: View validity
Next Topic: SQL Query Question
Goto Forum:
  


Current Time: Mon Dec 05 06:45:44 CST 2016

Total time taken to generate the page: 0.13622 seconds