?
Solved

oracle query

Posted on 2014-04-30
4
Medium Priority
?
314 Views
Last Modified: 2014-04-30
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
0
Comment
Question by:anumoses
  • 2
  • 2
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40032266
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')
0
 
LVL 6

Author Comment

by:anumoses
ID: 40032489
This query works perfectly fine.
 Can we add another column totals for each day of the week
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40032505
>> Can we add another column totals for each day of the week

You can add whatever you want.  Should just be a regular count?

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),
    count(*)
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')
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40032901
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question