asked on

oracle query

https://www.experts-exchange.com/questions/28423193/oracle-query.html

select drive_date,TO_CHAR(DRIVE_DATE,'Day') day_of_week,
count(case when COACH_DRIVE = 'C' then 'C' end ) coach,
count(case when COACH_DRIVE = 'M' then 'M' end ) mini, count(*) totals
from blood_drives
where drive_date between '01-jun-2014' and '31-aug-2014'
and coach_drive in ('C','M')
and drive_cancelled is null
group by drive_date,TO_CHAR(DRIVE_DATE,'Day')
order by drive_date

Users keep changing what they need. So entering another question. Will close the previous one. That worked. Now they need

They need ALL the Sunday’s totals added together, all the Monday’s, etc.
coach and mini added together.

slightwv (䄆 Netminder)

>>They need ALL the Sunday’s totals added together, all the Monday’s, etc.
coach and mini added together.

What is different here than your other question?

What does the new expected results look like?

anumoses

ASKER

Attaching the file
Drives-by-Day-of-the-Week.xls

Wasim Akram Shaik

==>They need ALL the Sunday’s totals added together, all the Monday’s, etc.
coach and mini added together.

Are they asking roll up total for day wise, try something like this

select drive_date,TO_CHAR(DRIVE_DATE,'Day') day_of_week,
count(case when COACH_DRIVE = 'C' then 'C' end ) coach,
count(case when COACH_DRIVE = 'M' then 'M' end ) mini, count(*) totals
from blood_drives
where drive_date between '01-jun-2014' and '31-aug-2014'
and coach_drive in ('C','M')
and drive_cancelled is null
group by drive_date,rollup(TO_CHAR(DRIVE_DATE,'Day'))
order by drive_date

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

perfect

PortletPaul

>>"rollup might be the way to go"

doubt it, "group by rollup" will introduce rows for the day totals, not an extra column