Home » SQL & PL/SQL » SQL & PL/SQL » How to generate SQL query
How to generate SQL query [message #242594] Mon, 04 June 2007 04:50 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
Stream Processed Date Day Calls

MMS 07/05/0700:00:00 Mon 101
MMS 07/05/0701:00:00 Mon 788
MMS 14/05/0700:00:00 Mon 77
MMS 21/05/0700:00:00 Mon 67
MMS 28/05/0700:00:00 Mon 45
MMS 04/06/0700:00:00 Mon 56
MMS 11/06/0700:00:00 Mon 561
GPRS 07/05/0700:00:00 Mon 1011
GPRS 07/05/0701:00:00 Mon 111
GPRS 14/05/0700:00:00 Mon 771
and so on......

Above is example data, there will be 24 hours and many streams.

To work out the average number of Calls for each stream each hour and day

then the result for the query will be


Stream Hour Day Average

MMS 00 Mon 151.5 -- (101 + 77 + 67 + 45+ 56+ 561)/6
MMS 01 Mon 788
and so on.....

This is the qery to do the above result

SELECT stream, to_char(processed_date,'hh24') as hour,
to_char(processed_date,'d') as dayofweek, avg(total)
FROM table1
GROUP BY stream, to_char(processed_date,'hh24') ,
to_char(processed_date,'d');


However I want to calculate the average by a different method
i.e to sort the Calls (for each stream, hour and day) in Descending order and
chop 2 values from the start and chop 2 values at the end then do the average for the remaining numbers).
For example

BEFORE

Stream Hour Day Average

MMS 00 Mon 151.5


NEW QUERY NEEDS TO DO THE FOLLOWING

Stream Hour Day Average

MMS 00 Mon 72 -- (67 + 77)/2


sort the values in descending order (45, 56,67,77,101,561)
Chop 2 values from the start and end so you will have only the remaining values 67 and 77
Take average of 67 and 77 so the average is 72


I need a query to do this, can anyone help?

[Updated on: Thu, 07 June 2007 08:04] by Moderator

Report message to a moderator

Re: HELP HELP: How to generate SQL query [message #242607 is a reply to message #242594] Mon, 04 June 2007 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and apply How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Please post your Oracle version (4 decimals).

Regards
Michel
Re: HELP HELP: How to generate SQL query [message #243245 is a reply to message #242594] Wed, 06 June 2007 10:40 Go to previous message
techno
Messages: 44
Registered: October 2003
Member
You can modify your query as below to ignore 2 records of eah higer and lower and
SELECT stream, to_char(processed_date,'hh24') as hour, 
to_char(processed_date,'d') as dayofweek, avg(total) 
FROM table1 t1
where 2 <= (select count(*) table1
            where stream = t1.stream and
		  trunc(processed_date) = trunc(t1.processed_date) and 
		  to_char(processed_date,'HH24') = to_char(processed_date,'HH24') and
		  total > t1.total   ) and
      2 <=  (select count(*) table1
             where stream = t1.stream and
	           trunc(processed_date) = trunc(t1.processed_date) and 
	           to_char(processed_date,'HH24') = to_char(processed_date,'HH24') and
		   total < t1.total   ) 		 
GROUP BY stream, to_char(processed_date,'hh24') , 
to_char(processed_date,'d')		



However, with the following restrictions

1. Atleast there are five distinct totals for each group. Otherise all the records will be ignored
2. If there are repeated values in the rows to be ignored, all will be ignored (for example in the list 10 12 12 15 16 20 25 28, the records 10 12 12 and 25 28 are ignored)


regards

Techno
Previous Topic: insert data unless its already inserted
Next Topic: Invalid DDL operation in system triggers
Goto Forum:
  


Current Time: Sun Dec 04 15:03:41 CST 2016

Total time taken to generate the page: 0.04267 seconds