CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Carlos Massucci
Carlos Massucci

Reputation: 88

How to Summing columns in a Oracle SQL Query?

I had a query to get a specific report in Ms Access. This query resumes the operation of a equipament reading the data, giving it "1/2" which means half a minute, or 0 depending the situation, and summing the lines.

Now I have to use it on Oracle BD. I did translate almost everyting except for the following line:

Inoperante + standby as parado,

For some reason I can't sum the coluns that I have selected using their names. Is there a specific way to do it in Oracle?

There is the entire Query: (Amateur here, don't laugh lol)

SELECT 

TO_CHAR(e3timestamp,'DD/MM/YYYY') as Data,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE 
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
(CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
 (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 0 ELSE 1/2 END)
ELSE
 (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 0 ELSE 1/2 END)
END)
   END)
  END)
 END)
)
AS Operando,

SUM((CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 1/2 ELSE 0 END)) AS TSP,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN 
  (CASE WHEN ARAUCARIA.Status=0 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS Inoperante,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN
  (CASE WHEN ARAUCARIA.SB=1 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS standby,

--Inoperante + standby as parado,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
    (CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
     (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 1/2 ELSE 0 END)
    ELSE
     (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 1/2 ELSE 0 END)
    END)
   END)
  END)
 END)
) AS Inadequada,

--Operando + TSP + Inadequada + Inoperante + standby as Total

FROM ARAUCARIA

--WHERE ARAUCARIA.E3TIMESTAMP BETWEEN  #<%DateIniMed1%>#  AND  #<%DateFimMed1%>#

GROUP BY TO_CHAR(e3timestamp,'DD/MM/YYYY')

Upvotes: 0

Views: 76

Answers (1)

xQbert
xQbert

Reputation: 35343

One can not reference the columns alias used in a select in the SAME select. You either have to

  1. spell out the entire formula for the column
  2. use a subquery
  3. use a Common Table Expression (CTE)

as a CTE: 

With CTE AS (
SELECT 

TO_CHAR(e3timestamp,'DD/MM/YYYY') as Data,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE 
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
(CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
 (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 0 ELSE 1/2 END)
ELSE
 (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 0 ELSE 1/2 END)
END)
   END)
  END)
 END)
)
AS Operando,

SUM((CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 1/2 ELSE 0 END)) AS TSP,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN 
  (CASE WHEN ARAUCARIA.Status=0 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS Inoperante,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN
  (CASE WHEN ARAUCARIA.SB=1 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS standby,

--Inoperante + standby as parado,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
    (CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
     (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 1/2 ELSE 0 END)
    ELSE
     (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 1/2 ELSE 0 END)
    END)
   END)
  END)
 END)
) AS Inadequada,

--Operando + TSP + Inadequada + Inoperante + standby as Total

FROM ARAUCARIA

--WHERE ARAUCARIA.E3TIMESTAMP BETWEEN  #<%DateIniMed1%>#  AND  #<%DateFimMed1%>#

GROUP BY TO_CHAR(e3timestamp,'DD/MM/YYYY'))

SELECT c.*
     , Inoperante + standby as parado
     , Operando + TSP + Inadequada + Inoperante + standby as Total
FROM cte C

as a subquery:

SELECT c.*
     , Inoperante + standby as parado
     , Operando + TSP + Inadequada + Inoperante + standby as Total
FROM (SELECT TO_CHAR(e3timestamp,'DD/MM/YYYY') as Data,
      SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE 
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
(CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
 (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 0 ELSE 1/2 END)
ELSE
 (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 0 ELSE 1/2 END)
END)
   END)
  END)
 END)
)
AS Operando,

SUM((CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 1/2 ELSE 0 END)) AS TSP,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN 
  (CASE WHEN ARAUCARIA.Status=0 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS Inoperante,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]>50 THEN
  (CASE WHEN ARAUCARIA.SB=1 THEN 1/2 ELSE 0 END)
 ELSE 0 END)
) AS standby,

--Inoperante + standby as parado,

SUM(
 (CASE WHEN ARAUCARIA.[Tempo por Stroke_Quality]<50 THEN 0 ELSE
  (CASE WHEN ARAUCARIA.Status=0 THEN 0 ELSE
   (CASE WHEN ARAUCARIA.SB=1 THEN 0 ELSE
    (CASE WHEN ARAUCARIA.VAZÃO=0 OR ARAUCARIA.VAZÃO=NULL THEN
     (CASE WHEN ARAUCARIA.[Tempo por Stroke]>15 THEN 1/2 ELSE 0 END)
    ELSE
     (CASE WHEN ARAUCARIA.[Quantidade inejtada]<10 THEN 1/2 ELSE 0 END)
    END)
   END)
  END)
 END)
) AS Inadequada,

--Operando + TSP + Inadequada + Inoperante + standby as Total

FROM ARAUCARIA

--WHERE ARAUCARIA.E3TIMESTAMP BETWEEN  #<%DateIniMed1%>#  AND  #<%DateFimMed1%>#

GROUP BY TO_CHAR(e3timestamp,'DD/MM/YYYY')) C

I'll let you figure out the replicating the formula one :P

Upvotes: 2

Related Questions