Home » SQL & PL/SQL » SQL & PL/SQL » Grouping by Saturday and Sunday (Oracle XE)
Grouping by Saturday and Sunday [message #319635] Mon, 12 May 2008 07:35 Go to next message
rahul15june
Messages: 35
Registered: May 2008
Member
Hi Guys,

I wanted to sum data based on weekends (Saturday and Sunday only). Is it possible. Here is the sample data,

exp_date exp_amt
01-MAY-2008 100
01-MAY-2008 400
02-MAY-2008 500
03-MAY-2008 300
03-MAY-2008 100
04-MAY-2008 100
.
.
31-MAY-2008 100


The output should be only,

Week1 500 (since 3rd and 4th may are saturday sunday we take sum)

week2 0 (since no expense on may 10th and 11th)

week3 0 (since no expense on may 17 and 18th)

week4 0 (since no expense on may 24th and 25th)

week5 0 (since no expense on may 31st)

So idea is to get the summation of weekend amount for saturday and sunday in a week for a month in a year.

Thanks,
Rahul
Re: Grouping by Saturday and Sunday [message #319650 is a reply to message #319635] Mon, 12 May 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To get each week-end (even those you have no data), search for "calendar" or "row genetator".

To get day date, have a look at TO_CHAR

Regards
Michel
Re: Grouping by Saturday and Sunday [message #319659 is a reply to message #319635] Mon, 12 May 2008 08:58 Go to previous messageGo to next message
manish1729
Messages: 1
Registered: December 2007
Junior Member
Use ...

select 'Week '|| to_char(exp_date,'IW'),exp_amt from table_name


Regards
Manish M

[Updated on: Mon, 12 May 2008 08:59]

Report message to a moderator

Re: Grouping by Saturday and Sunday [message #319681 is a reply to message #319659] Mon, 12 May 2008 10:15 Go to previous message
rahul15june
Messages: 35
Registered: May 2008
Member
Thanks Manish,

That worked. I was using 'W' parameter. But 'IW' would be a better option in terms of its usage and my requirement. thanks


Cheers,
Rahul
Previous Topic: Delete operation and unable to use trigger
Next Topic: need to remove sql query
Goto Forum:
  


Current Time: Thu Dec 08 00:06:00 CST 2016

Total time taken to generate the page: 0.06592 seconds