asked on

oracle query

select drive_date,drive_id,to_char(drive_date,'Day'),coach_drive
from blood_drives
where drive_date between '01-jun-2014' and '31-aug-2014'
and coach_drive in ('C','M')

DRIVE_DATE,DRIVE_ID,TO_CHAR(DRIVE_DATE,'DAY'),COACH_DRIVE
6/1/2014,2024075,Sunday ,C
6/1/2014,2023621,Sunday ,C
6/2/2014,2024011,Monday ,M
6/2/2014,2024078,Monday ,C
6/2/2014,2023437,Monday ,M
6/2/2014,2023211,Monday ,M
6/3/2014,2024083,Tuesday ,C
6/3/2014,2024084,Tuesday ,C
6/3/2014,2023684,Tuesday ,C
6/3/2014,2024079,Tuesday ,C
6/4/2014,2024164,Wednesday,C
6/4/2014,2024158,Wednesday,C
6/4/2014,2024159,Wednesday,C
6/4/2014,2024160,Wednesday,C
6/5/2014,2023975,Thursday ,C
6/5/2014,2023161,Thursday ,C
6/5/2014,2024166,Thursday ,C
6/6/2014,2024028,Friday ,C
6/7/2014,2022771,Saturday ,C
6/7/2014,2024167,Saturday ,C
6/7/2014,2023431,Saturday ,C
6/8/2014,2024169,Sunday ,C
6/8/2014,2024170,Sunday ,C
6/9/2014,2024447,Monday ,C
6/9/2014,2024485,Monday ,C
6/9/2014,2022854,Monday ,M
6/9/2014,2024172,Monday ,C
6/9/2014,2023246,Monday ,C

The user want this report in this format

Day of the week count(coaches) count(minis)

Sunday 2
Monday 1 3
Tuesday 4
Wednesday 4

----- etc

slightwv (䄆 Netminder)

Try this:

select TO_CHAR(DRIVE_DATE,'DAY'),
count(case when COACH_DRIVE = 'C' then 'C' end),
count(case when COACH_DRIVE = 'M' then 'M' end)
from blood_drives
where drive_date between '01-jun-2014' and '31-aug-2014'
and coach_drive in ('C','M')
group by TO_CHAR(DRIVE_DATE,'DAY')

anumoses

ASKER

This query works perfectly fine.
Can we add another column totals for each day of the week

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

thanks