Sum of rows on Saturdays and Sundays

Question

I am trying to get the count of rows from my table for a day like Saturday or Sunday.

The code below is not working.

Any idea how to achieve this?

SELECT next_day(sysdate-8, 'SUN') from dual;

NEXT_DAY(SYSDATE-8,)
-------------------
2014/11/02 21:22:30

My attempt:

select sum(QTY),GROUP from table where next_day(order_date-8, 'SUN')  group by GROUP;

What is your problem? You should edit your question and add sample data and desired results. — Gordon Linoff, Nov 05 '14 at 21:32
Not a real duplicate, but very related to what I think you are trying to achieve:[Determine if Oracle date is on a weekend](http://stackoverflow.com/questions/3450965/determine-if-oracle-date-is-on-a-weekend). — GolezTrol, Nov 05 '14 at 21:34

Maheswaran Ravisankar · Accepted Answer · 2014-11-07T11:40:36.137

3

To filter only Sundays/Saturdays you might need TO_CHAR()

select sum(QTY),GROUP
from table
where TO_CHAR(order_date,'DY', 'NLS_DATE_LANGUAGE=AMERICAN') in ('SUN','SAT')
group by GROUP;

edited Nov 07 '14 at 11:40

answered Nov 05 '14 at 21:33

Maheswaran Ravisankar

17,652
6
47
69

1

Warning: This is no water proof solution, since the result of to_char depends on the NLS settings. There are numerous answers on StackOverflow that provide NLS-agnostic ways to do this. – GolezTrol Nov 05 '14 at 21:35
thanks tons,its working, order_date is partition_key as its being used as function now. this query is going full table scan. is there any way to use order_date without using to_char function ? – poster_boy Nov 05 '14 at 23:23
2

Better use `TO_CHAR(order_date,'DY', 'NLS_DATE_LANGUAGE=AMERICAN')` in order to be independent from NLS settings. – Wernfried Domscheit Nov 06 '14 at 09:05

score 0 · Answer 2 · answered Nov 06 '14 at 04:22

I think you could use the NEXT_DAY() function as follows:

SELECT SUM(qty), group
  FROM table
 WHERE order_date = NEXT_DAY(order_date - 1, 'SAT')
    OR order_date = NEXT_DAY(order_date - 1, 'SUN')
 GROUP BY group

That might even avoid the full table scan if you have an index on the order_date column. I just tried it on a similar table and using the TO_CHAR() method above I got a full table scan, while using this method here I got a full scan of the index on the date column.

Sum of rows on Saturdays and Sundays

2 Answers2