CODEWITHSUNDEEP
sqloracle-database
poster_boy
poster_boy

Reputation: 23

Sum of rows on Saturdays and Sundays

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;

Upvotes: 2

Views: 184

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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;

Upvotes: 3

David Faber
David Faber

Reputation: 12485

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.

Upvotes: 0

Related Questions