CODEWITHSUNDEEP
sqloracle-database
Abdusoli
Abdusoli

Reputation: 659

how to use double group by and sum statements together in oracle

I am trying to use query below but it is giving an error


SELECT s.LOCAL_CODE,substr(p.ACCOUNT_CREDIT,-3),(p.SUMMA/100) as profit
        FROM OPERATIONS s INNER JOIN LEADS p ON s.PAY_ID = p.PAY_ID
            WHERE s.date_paid >= TO_DATE('03.12.2019', 'DD.MM.YYYY')
               AND s.date_paid < TO_DATE('03.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY
               AND state = 'T'
               AND s.filial_code = '006789'
               AND SUBSTR(p.ACCOUNT_CREDIT, 1, 5) = '765294'
    GROUP BY s.LOCAL_CODE,substr(p.ACCOUNT_CREDIT,-3);

Upvotes: 1

Views: 130

Answers (1)

Adam Silenko
Adam Silenko

Reputation: 3108

If LEADS.SUMMA has expected value then you don't need Group By clause, else if you use Group By then all not grouped fields can be used only as arguments of aggregate functions:

SELECT s.LOCAL_CODE
, Substr(p.ACCOUNT_CREDIT, -3)
, Sum(p.SUMMA)/100 as profit
FROM OPERATIONS s 
INNER JOIN LEADS p ON s.PAY_ID = p.PAY_ID
WHERE s.date_paid >= TO_DATE('03.12.2019', 'DD.MM.YYYY')
AND s.date_paid < TO_DATE('03.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY
AND state = 'T'
AND s.filial_code = '006789'
AND SUBSTR(p.ACCOUNT_CREDIT, 1, 5) = '765294'
GROUP BY s.LOCAL_CODE
, substr(p.ACCOUNT_CREDIT, -3);

Upvotes: 1

Related Questions