Home » SQL & PL/SQL » SQL & PL/SQL » Query for Average , sum based on Type
Query for Average , sum based on Type [message #280207] Mon, 12 November 2007 16:27 Go to next message
speaker
Messages: 30
Registered: April 2006
Member
Hi,

we have a Table Description

CREATE TABLE USAGE_DATA
(
  USERID         VARCHAR2(32 BYTE),
  CELL_NUMBER    INTEGER,
  CALL_TYPE      VARCHAR2(16 BYTE),
  CALL_DURATION  INTEGER,
)


The Data will look like following

USERID CELL_NUMBER CALL_TYPE CALL_DURATION
ashish 7032098405 DATA 32
ashish 7032098405 VOICE 3
ashish 7032098405 VOICE 32
ashish 7032098405 VOICE 8
goura 7032095234 DATA 32
goura 7032095234 DATA 43
goura 7032095234 DATA 52
goura 7032095234 VOICE 52
goura 7032095234 VOICE 52


The user has made voice and data calls and their durations are stored in the table

now i want the following info for the report ...

UserID
Mobile No
# Total Calls
# Voice Calls Duration
# Data Calls Duration
Average Data Call Duration
Average Voice Call Duration

group by Userid,mobileno


I wrote the query and achieved the results.


SELECT userid, no_calls, no_datacalls, no_voicecalls, total_duration,
       TRUNC (data_callduration / no_datacalls, 2) avg_data,
       TRUNC (voice_callduration / no_voicecalls, 2) avg_voice
  FROM (SELECT   userid, COUNT (*) no_calls,
                 SUM (DECODE (call_type, 'DATA', 1, 0)) no_datacalls,
                 SUM (DECODE (call_type, 'VOICE', 1, 0)) no_voicecalls,
                 SUM (call_duration) total_duration,
                 SUM (DECODE (call_type, 'DATA', call_duration, 0)
                     ) data_callduration,
                 SUM (DECODE (call_type, 'VOICE', call_duration, 0)
                     ) voice_callduration
            FROM usage_data
        GROUP BY userid, cell_number)



is there any way for a simpler query

[Updated on: Mon, 12 November 2007 16:29]

Report message to a moderator

Re: Query for Average , sum based on Type [message #280240 is a reply to message #280207] Mon, 12 November 2007 23:11 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

What about ..

		SELECT   userid, COUNT (*) no_calls,
                 SUM (DECODE (call_type, 'DATA', 1, 0)) no_datacalls,
                 SUM (DECODE (call_type, 'VOICE', 1, 0)) no_voicecalls,
                 SUM (call_duration) total_duration,
                 TRUNC(AVG (DECODE (call_type, 'DATA', call_duration, NULL)
                     ),2) avg_data,
                 TRUNC(AVG (DECODE (call_type, 'VOICE', call_duration, NULL)
                     ),2)  avg_voice
            FROM ORAFAQ_usage_data
        GROUP BY userid, cell_number



Thumbs Up
Rajuvan

[Updated on: Mon, 12 November 2007 23:12]

Report message to a moderator

Previous Topic: Any function to calculate weekends...
Next Topic: tuples
Goto Forum:
  


Current Time: Wed Dec 07 18:35:57 CST 2016

Total time taken to generate the page: 0.05401 seconds