how to group and sum up certain fields [message #439235] |
Fri, 15 January 2010 03:42  |
pyscho
Messages: 134 Registered: December 2009
|
Senior Member |
|
|
What i'm trying to do is group my records by a certain match so take this example. Transactions table:
Fields are: USER_ID | ACCOUNT_TYPE | ACCOUNT_SUFFIX | PAID | AMOUNT
Example contents:
7437658 | DEBITNEW | F | Yes | 10.00
7437658 | DEBITNEW | F | Yes | 30.00
7437658 | DEBITTIER1 | C | Yes | 15.00
7437658 | DEBITTIER1 | C | Yes | 5.00
3957389 | DEBITNEW | A | Yes | 35.00
What i want to do is group by the USER_ID, ACCOUNT_TYPE and ACCOUNT_SUFFIX, sum up the totals, and insert into another table. so the above should do something like this
INSERT INTO ANOTHER_TABLE (TRANSACTION_ID, AMOUNT)
VALUES ( '7437658.DEBITNEW.F', '40.00')
INSERT INTO ANOTHER_TABLE (TRANSACTION_ID, AMOUNT)
VALUES ( '7437658.DEBITTIER1.C', '20.00')
INSERT INTO ANOTHER_TABLE (TRANSACTION_ID, AMOUNT)
VALUES ( '3957389.DEBITNEW.A', '35.00')
(Or if possible, 1 big insert, to save processing time?)
could someone show me how this could be done?
[Updated on: Fri, 15 January 2010 03:44] Report message to a moderator
|
|
|
Re: how to group and sum up certain fields [message #439236 is a reply to message #439235] |
Fri, 15 January 2010 03:52   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looks like you want to group by the concatenation of USER_ID, ACCOUNT_TYPE and ACCOUNT_SUFFIX and sum the amount. Should be really simple. Code the select then turn it into an insert. Try it, if you get stuck let us know and we'll help you out.
I would question the table structure of the other table though. Combining three fields into one is just going to make your life difficult when it comes to joining data and ensuring data integrity.
|
|
|
|
|
|
|
|
Re: how to group and sum up certain fields [message #439244 is a reply to message #439243] |
Fri, 15 January 2010 05:02   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Maaher wrote on Fri, 15 January 2010 10:50The concatenation operator (double pipe, '||') concatenates character strings, whether it be literals or column values, so you could concatenate the tilde with the rest of your string.
@cookiemonster: you can group by the separate columns as well, if I'm not mistaken.
MHE
You can indeed. Never occured to me since I just view concat as a function and normally if you select a function you need to group by it or you'll get odd results - trunc for example. In the case of concat I assume it won't affect the final result.
That said I would imagine not grouping by the concatted expression might confuse some people so I'd always do it that way.
|
|
|
|
|
Re: how to group and sum up certain fields [message #439250 is a reply to message #439235] |
Fri, 15 January 2010 06:14  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Thanks cookiemonster...
here is an example
SQL> set timing on
SQL> with t1 as
2 (select 1 a, to_date('1/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') b from du
al
3 union all
4 select 1 a, to_date('1/1/2009 2:00:00','mm/dd/yyyy hh24:mi:ss') b from dual
5 union all
6 select 2 , to_date('2/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual
7 union all
8 select 3, to_date('3/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual
9 union all
10 select 4, to_date('4/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual)
11 select sum(a),trunc(b) from t1
12 group by b;
SUM(A) TRUNC(B)
---------- ---------
1 01-JAN-09
1 01-JAN-09
2 01-FEB-09
3 01-MAR-09
4 01-APR-09
Elapsed: 00:00:00.02
SQL> with t1 as
2 (select 1 a, to_date('1/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') b from du
al
3 union all
4 select 1 a, to_date('1/1/2009 2:00:00','mm/dd/yyyy hh24:mi:ss') b from dual
5 union all
6 select 2 , to_date('2/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual
7 union all
8 select 3, to_date('3/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual
9 union all
10 select 4, to_date('4/1/2009 12:00:00','mm/dd/yyyy hh24:mi:ss') from dual)
11 select sum(a),trunc(b) from t1
12 group by trunc(b);
SUM(A) TRUNC(B)
---------- ---------
2 01-FEB-09
3 01-MAR-09
2 01-JAN-09
4 01-APR-09
Elapsed: 00:00:00.02
SQL>
The first one is without trunc and the second one is with trunc in the group by clause.
Regards,
Pointers
|
|
|