Home » SQL & PL/SQL » SQL & PL/SQL » how to group and sum up certain fields
how to group and sum up certain fields [message #439235] Fri, 15 January 2010 03:42 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12409
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 #439239 is a reply to message #439236] Fri, 15 January 2010 04:05 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I know the GROUP BY , but I cant find any docs that GROUP BY CONCATENATION
Re: how to group and sum up certain fields [message #439240 is a reply to message #439235] Fri, 15 January 2010 04:19 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can group by functions same as columns:
SELECT col1||col2 as concatcols, sum(col3)
FROM table
GROUP BY col1||col2
Re: how to group and sum up certain fields [message #439241 is a reply to message #439240] Fri, 15 January 2010 04:36 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
hmm many thanks for the reply. what if i wanted to insert with a delimiting tilda like this

7437658~DEBITTIER1~C
Re: how to group and sum up certain fields [message #439242 is a reply to message #439241] Fri, 15 January 2010 04:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have you tried it? What happened?
Re: how to group and sum up certain fields [message #439243 is a reply to message #439242] Fri, 15 January 2010 04:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The 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
Re: how to group and sum up certain fields [message #439244 is a reply to message #439243] Fri, 15 January 2010 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maaher wrote on Fri, 15 January 2010 10:50
The 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 #439245 is a reply to message #439244] Fri, 15 January 2010 05:48 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
SELECT col1||'~'||col2 as concatcols, sum(col3)
FROM table
GROUP BY col1||'~'||col2


Would i basically wrap an INSERT around the select query above?
Re: how to group and sum up certain fields [message #439246 is a reply to message #439235] Fri, 15 January 2010 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes - just try it, what's the worst that could happen?
Re: how to group and sum up certain fields [message #439250 is a reply to message #439235] Fri, 15 January 2010 06:14 Go to previous message
pointers
Messages: 410
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
Previous Topic: How to reduce the column length (CHAR) ? [split topic TG]
Next Topic: Converting Number Inputs to Their corresponding Word-Values
Goto Forum:
  


Current Time: Wed Dec 07 03:24:33 CST 2016

Total time taken to generate the page: 0.22378 seconds