?
Solved

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

Posted on 2004-09-15
5
Medium Priority
?
2,635 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

771 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