Solved

SQL Problem Need to query for specific day of week based on original query below.

Posted on 2004-09-15
5
2,621 Views
Last Modified: 2009-12-16
/*
Assumed table structure
 
CREATE TABLE BATCHCATALOG_TEMP
(
  CORP              NUMBER(5),
  LOAD_DATE         DATE,
  BOGUS_INFO        VARCHAR(10)
 
Contains unique records - all columns in key
 
*/
 
-- This is the original query
 
select trunc(load_date),to_char(load_date,'DAY'), count(1)
from table1
where corp >= 7801
and corp <= 7899
and load_date >= to_date('26-aug-2004')
and load_date < sysdate
group by trunc(load_date),to_char(load_date,'DAY')
 

-- This is the aggregation query that I wanted to use
-- but it gets this error: 00937: not a single-group group function
--
select to_char(load_date,'DAY'), avg(count(1))
from table1
where corp >= 7801
and corp <= 7899
and load_date >= to_date('&Enter_Begin_Date')
and load_date < sysdate
group by to_char(load_date,'DAY')
 
-- This is the amended query to try to get it to work
-- but i got this error: ORA-00978: a nested group function without GROUP BY
--
select avg(count(1))
from table1
where corp >= 7801
and corp <= 7899
and load_date >= to_date('&Enter_Begin_Date')
and load_date < sysdate
 
-- This is the amended version of the above query which works
--
select avg(count(1))
from table1
where corp >= 7801
and corp <= 7899
and load_date >= to_date('&Enter_Begin_Date')
and load_date < sysdate
group by to_char(load_date,'DAY')


*****************************************************
-- The ultimate goal is to get a average count per day of the week
-- basically an aggregation of the first query
 
 ****************************************************
0
Comment
Question by:jgiordano
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 500 total points
ID: 12066415
How about:

select day, avg(cnt) from
(
select trunc(load_date) ld, to_char(load_date,'DAY') day, count(1) cnt
from table1
where corp >= 7801
and corp <= 7899
and load_date >= to_date('26-aug-2004')
and load_date < sysdate
group by trunc(load_date),to_char(load_date,'DAY')
)
group by day;
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12067096
jGiodiano:

I hope I understand what you are trying to do...It does not make any sense to me now...

have you realized AVG(COUNT(1)) = COUNT(1) in your case?

Andrewst's query will work, but you are looking for something you don't intend for...

Your original query should work perfectly for you...
I mean even if you apply AVG function over COUNT(1) in the end, you will get the exact same result as your original query.

I can bet on it..
0
 
LVL 11

Author Comment

by:jgiordano
ID: 12067728
What we are trying to get is the average number of transactions for each day of the week FOR the period specified (i.e. 45 days)
Summarize the average # of records for mondays, tuesdays, wednesdays... during the last 45 days.

Here is the data from the 1st query unaggregated:

TRUNC(LOAD_DATE)      TO_CHAR(LOAD_DATE,'DAY')      COUNT(1)
8/26/2004      THURSDAY       33782
8/27/2004      FRIDAY         16448
8/28/2004      SATURDAY       50975
8/29/2004      SUNDAY         18102
8/30/2004      MONDAY         7940
8/31/2004      TUESDAY        45911
9/1/2004      WEDNESDAY      33183
9/2/2004      THURSDAY       36767
9/3/2004      FRIDAY         38164
9/4/2004      SATURDAY       32801
9/5/2004      SUNDAY         20388
9/6/2004      MONDAY         15230
9/7/2004      TUESDAY        22736
9/8/2004      WEDNESDAY      33139
9/9/2004      THURSDAY       45202
9/10/2004      FRIDAY         44305
9/11/2004      SATURDAY       36637
9/12/2004      SUNDAY         22991
9/13/2004      MONDAY         37044
9/14/2004      TUESDAY        74883
9/15/2004      WEDNESDAY      31696
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12069667
my apology, my misunderstanding in that case.

Andrewst's solution should work fine for you...
0
 
LVL 11

Author Comment

by:jgiordano
ID: 12071243
I am a validating the data but it looks like andrews worked. I will close this out tomorrow.

Thanks guys
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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