Vadim Vadim - 2 months ago 6
SQL Question

group some row in resilt table

I need get a group some row select from result table. I've written select but have error message ORA-00913. How to write a correct select ?I have a
result table:

DROP TABLE MONEY_ACTIVITY;
CREATE TABLE MONEY_ACTIVITY (
ID NUMBER(15) NOT NULL,
DOCUMENT_NUMBER VARCHAR2(13) NULL,
ID_CUSTOMER NUMBER NULL,
BASE_AMOUNT NUMBER(5,2) NULL
);


and insert:

INSERT INTO MONEY_ACTIVITY VALUES (1, 'input_saldo', 111222, 300);
INSERT INTO MONEY_ACTIVITY VALUES (2, 'input_saldo', 222333, 600);
INSERT INTO MONEY_ACTIVITY VALUES (3, 'ouput_saldo', 333444, 400);
INSERT INTO MONEY_ACTIVITY VALUES (4, 'ouput_saldo', 444555, 600);
INSERT INTO MONEY_ACTIVITY VALUES (5, 'TN0001', 444555, 600);
INSERT INTO MONEY_ACTIVITY VALUES (6, 'TN0002', 444555, 700);
INSERT INTO MONEY_ACTIVITY VALUES (7, 'TN0003', 333444, 600);
INSERT INTO MONEY_ACTIVITY VALUES (8, 'TN0004', 333444, 700);
INSERT INTO MONEY_ACTIVITY VALUES (9, 'TN0005', 111444, 600);
INSERT INTO MONEY_ACTIVITY VALUES (10, 'TN0006', 222444, 700);


and I need to get result table:

input_saldo 900
ouput_saldo 1000
TN0001 600
TN0002 700
TN0003 600
TN0004 700
TN0005 600
TN0006 700


I'm trying:

select
case when DOCUMENT_NUMBER = 'input_saldo' then (select DOCUMENT_NUMBER, SUM(BASE_AMOUNT)
from MONEY_ACTIVITY
where DOCUMENT_NUMBER = 'input_saldo'
group by DOCUMENT_NUMBER) end case,
case when DOCUMENT_NUMBER = 'ouput_saldo' then (select DOCUMENT_NUMBER, SUM(BASE_AMOUNT)
from MONEY_ACTIVITY
where DOCUMENT_NUMBER = 'ouput_saldo'
group by DOCUMENT_NUMBER) end case,
DOCUMENT_NUMBER,
BASE_AMOUNT
from MONEY_ACTIVITY
where DOCUMENT_NUMBER NOT IN ('input_saldo', 'output_saldo');


but ora-00913 too many values

How to write correct "select" ?

Answer

Why not use simple query ?

SELECT DOCUMENT_NUMBER,SUM(BASE_AMOUNT)
FROM MONEY_ACTIVITY
GROUP BY DOCUMENT_NUMBER