Solved

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

Posted on 2004-09-15
5
2,604 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.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now