Go Premium for a chance to win a PS4. Enter to Win

x
?
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,644 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 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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 …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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.

926 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