Reputation: 557
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" ?
Sorry, There is some new conditions. The table can contain rows with the same Document_number and different ID_customer. This rows must not to add up summarize.
Example:
1 input_saldo 111222 300 2 input_saldo 222333 600 4 ouput_saldo 444555 600 3 ouput_saldo 333444 400 11 TN0001 333444 600 --!!!!!!! 5 TN0001 444555 600 --!!!!!!! 6 TN0002 444555 700 ......
And I try execute:
SELECT DOCUMENT_NUMBER, ID_CUSTOMER, SUM(BASE_AMOUNT)
FROM MONEY_ACTIVITY
GROUP BY DOCUMENT_NUMBER, ID_CUSTOMER;
Return result is:
DOCUMENT_NUMBER ID_CUSTOMER SUM(BASE_AMOUNT)
input_saldo 222333 600
input_saldo 111222 300
ouput_saldo 333444 400
ouput_saldo 444555 600
TN0001 444555 600
TN0001 333444 600
TN0002 444555 700
TN0003 333444 600
TN0004 333444 700
TN0005 111444 600
TN0006 222444 700
But I need:
input_saldo 111222 900
ouput_saldo 444555 1000
TN0001 444555 600
TN0001 333444 600
TN0002 444555 700
TN0003 333444 600
TN0004 333444 700
TN0005 111444 600
TN0006 222444 700
Upvotes: 1
Views: 80
Answers (8)
Reputation: 11
You can apply an aggregating function, for example:
SELECT DOCUMENT_NUMBER, SUM(BASE_AMOUNT)
FROM MONEY_ACTIVITY
GROUP BY DOCUMENT_NUMBER
ORDER BY 1
Upvotes: 1
Reputation: 557
select
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then DOCUMENT_NUMBER else DOCUMENT_NUMBER_1 end DOCUMENT_NUMBER,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then BASE_AMOUNT else BASE_AMOUNT_1 end BASE_AMOUNT,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then null else ID_CUSTOMER_1 end ID_CUSTOMER
from
(select DOCUMENT_NUMBER,
sum(BASE_AMOUNT) BASE_AMOUNT,
DOCUMENT_NUMBER_1,
BASE_AMOUNT_1,
ID_CUSTOMER_1
from
(select
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then DOCUMENT_NUMBER else null end DOCUMENT_NUMBER,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then BASE_AMOUNT else null end BASE_AMOUNT,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then null else ID end ID_1,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then null else DOCUMENT_NUMBER end DOCUMENT_NUMBER_1,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then null else BASE_AMOUNT end BASE_AMOUNT_1,
case when DOCUMENT_NUMBER in ('input_saldo', 'ouput_saldo') then null else ID_CUSTOMER end ID_CUSTOMER_1
from MONEY_ACTIVITY)
group by DOCUMENT_NUMBER, DOCUMENT_NUMBER_1, BASE_AMOUNT_1, ID_CUSTOMER_1);
Upvotes: 0
Reputation: 12309
Why not use simple query ?
SELECT DOCUMENT_NUMBER,SUM(BASE_AMOUNT)
FROM MONEY_ACTIVITY
GROUP BY DOCUMENT_NUMBER
Upvotes: 0
Reputation: 1054
You have a subquery to populate a scalar value and the subquery is returning two values. As per MarinC answer above you could just remove DOCUMENT_NUMBER from the subqueries.
Your query also looks wrong as you appear to have mis-spelled 'output_saldo' and you are specifically excluding the two '...saldo' values so they will never appear in the result set anyway.
This does what you seem to be asking without using the sub-queries;
SELECT document_number, SUM(base_amount)
FROM money_activity
GROUP BY document_number
ORDER BY document_number desc
Upvotes: 0
Reputation: 306
Simply use GROUP BY-
SELECT DOCUMENT_NUMBER ,SUM(BASE_AMOUNT)
FROM MONEY_ACTIVITY
GROUP BY DOCUMENT_NUMBER
Upvotes: 1
Reputation: 22949
From your data sample, a simple aggregation does the work:
select DOCUMENT_NUMBER, sum(BASE_AMOUNT)
from MONEY_ACTIVITY
group by DOCUMENT_NUMBER
If you need a more complex logic, please edit your question to better explain your need
Upvotes: 0
Reputation: 133360
Not by the merits of the logic of the query limit myself only to the error reported .. subqueries return two columns while the result should contain only one
select
case when DOCUMENT_NUMBER = 'input_saldo' then (select 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 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');
Upvotes: 0
Reputation: 169
I think that error is becouse your subquery should return one value (sum(BASE_AMOUNT)). Remove column DOCUMENT_NUMBER from subquery.
Upvotes: 0