Sum multiple rows value in nested SQL Oracle query

Question

I want to sum all the rows of T.CLS AND CLS in below query but I am not able to as it's showing not a Sony group function. How to I resolve this?

SELECT A.PROD,
          A.ID AS ACCOUNT,
          A.NAME AS NAME,
          T.PDATE AS DATE,
          A.CODE,
          T.CLS,
          CASE
              WHEN A.CODE != 'abc' THEN
                     (SELECT DISTINCT col
                      FROM table1
                      WHERE CODE2 = A.CODE
                        AND TOCODE = 'ABC'
                        AND ETYPE = 'S') * T.CLS
              ELSE CLS
          END AS EQB
   FROM UTTABLE T JOIN ACTABLE A ON T.AID = A.ID

`T.CLS AND CLS` what is the second thing in this? both refers to same column, right? — Popeye, Oct 18 '19 at 05:59

score 2 · Answer 1 · answered Oct 18 '19 at 05:11

2

You can use analitic function of course:

SELECT A.PROD,
      A.ID AS ACCOUNT,
      A.NAME AS NAME,
      T.PDATE AS DATE,
      A.CODE,
      T.CLS,
      sum(T.CLS) over (partition by A.ID) "SUMM",
      count(T.CLS) over (partition by A.ID) "amount",
      CASE
          WHEN A.CODE != 'abc' THEN
                 (SELECT DISTINCT col
                  FROM table1
                  WHERE CODE2 = A.CODE
                    AND TOCODE = 'ABC'
                    AND ETYPE = 'S') * T.CLS
          ELSE CLS
      END AS EQB
FROM UTTABLE T JOIN ACTABLE A ON T.AID = A.ID

If you want using GROUP BY, then all fields(except grouping data) must write in GROUP BY

answered Oct 18 '19 at 05:11

Rustam Pulatov

625
1
9
20

You are welcome. Confirm, please, my answer as rigth – Rustam Pulatov Oct 18 '19 at 05:46
Is it possible to add multiple CASE in this query? – Rahul Anand Oct 18 '19 at 07:13
yes, of course. Analitics function using group only in "his window". It's very comfortably. – Rustam Pulatov Oct 18 '19 at 07:17

Sum multiple rows value in nested SQL Oracle query

1 Answers1