Solved

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

Posted on 2004-09-15
5
2,629 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

724 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