CODEWITHSUNDEEP
sqloracle-database
chinmay
chinmay

Reputation: 870

clubbing multiple "With" clauses in sql

I am using oracle database 10g and trying to compute the Upper control limit and lower control limit for the data set.Though it seems useless for phone number values but I am just trying to use it as a learning experience.The output should have a row wise form for entries of:- salutation,zip,lcl and ucl value which would allow better understanding of data.

with q as(

 select student_id,salutation,zip,first_name,last_name from tempTable)

 with r as(

 select avg(phone) as average,stddev(phone) as sd from tempTable)

 select salutation,zip,average-3*sd as"lcl",average+3*sd as"UCL"

from
q ,r

error given is select statement missing.Please tell me what is wrong I am a sql newbie and can't do it myself

Upvotes: 2

Views: 242

Answers (2)

David Faber
David Faber

Reputation: 12485

I don't think you need a WITH clause at all to run such a query. It might be better to use the AVG() and STDDEV() functions as window functions (analytic functions in Oracle lingo):

SELECT temp1.*, average - 3 * sd AS lcl, average + 3 * sd AS ucl
  FROM (
    SELECT student_id, salutation, zip, first_name, last_name
         , AVG(phone) OVER ( ) AS average, STDDEV(phone) OVER ( ) AS sd
      FROM tempTable
) temp1

You don't even need the subquery but it helps save some keystrokes. See this SQL Fiddle demo with dummy data from DUAL.

P.S. You do need the alias (in this case, temp1) for the subquery if you want to use * to get all the columns selected in the subquery - it won't work otherwise. Alternately you could name the columns explicitly, which is a good practice anyway.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

while using stacked CTE expect for the first CTE you don't need With keyword instead use comma before the CTE name. Try this syntax.

WITH q 
     AS (SELECT student_id, 
                salutation, 
                zip, 
                first_name, 
                last_name 
         FROM   temptable), 
     r 
     AS (SELECT Avg(phone)    AS average, 
                STDDEV(phone) AS sd 
         FROM   temptable) 
SELECT salutation, 
        zip, 
        average - 3 * sd AS"lcl", 
        average + 3 * sd AS"UCL" 
FROM   q Cross Join r;

Upvotes: 2

Related Questions