会sql的进来

Siangboy

1.
Aggregate the calls bythe telephone number they were made from, presenting the total number of phonecalls, total number of minutes, and the total charge.
2.
Based on the above,show the top 5 callers with respect to the total number of minutes.
3.
Show the total numberof calls made to normal telephone numbers (callsNormNumbers), the total numberof calls made to special-service numbers (callsSpecNumbers) and theirdifference (callsOrdNumbers - callsSpecNumbers), per month.

谁可以帮我写以上的SQL Expressions？？



[ 本帖最后由 Siangboy 于 12-3-2009 09:15 AM 编辑 ]

Siangboy

自己回答自己

1
SELECT FROMTELNO AS "TEL NO.", COUNT(*) AS "TOTAL NO. OF CALLS",
  ROUND(SUM(endtime - starttime),4) * 24 * 60 AS "NO. OF MINUTES",
  SUM(charge) AS "TOTAL CHARGE"
FROM telco_calls
GROUP BY fromtelno;

2
SELECT * FROM
(SELECT FROMTELNO AS "FROM TEL NO" , ROUND(SUM(endtime - starttime),4) * 24 * 60 AS "NO. OF MINUTES"
  FROM telco_calls
  GROUP BY fromtelno
  ORDER BY "NO. OF MINUTES" DESC)
WHERE ROWNUM <= 5;

3
SELECT "Month" AS "MONTH", callsNormNumbers AS "CALLS TO NORMAL NUMBER", callsSpecNumbers AS "CALLS TO SPECIAL NUMBER", callsNormNumbers - callsSpecNumbers AS "CALLS DIFFERENCE"
FROM
(SELECT to_char(starttime, 'MON') AS "Month", COUNT(TOAC) AS callsNormNumbers
FROM telco_calls
WHERE NOT(TOAC = 0870 OR TOAC = 0844 OR TOAC = 0900)
GROUP BY to_char(starttime, 'MON')),

(SELECT to_char(starttime, 'MON') AS month2, COUNT(TOAC) AS callsSpecNumbers
FROM telco_calls
WHERE TOAC = 0870 OR TOAC = 0844 OR TOAC = 0900
GROUP BY to_char(starttime, 'MON'))

WHERE "Month" = month2
ORDER BY "Month";