Help creating a SQL SELECT statement

Question

I have data in the following format in a table:

Acct#    Amount
123      3.4 
123T     4.5 
124      2.3 
124T     4.5 
125      1.2 
125T     2.4

How do I create a select statement where it totals up the account number as 123+123T and gives the following output:

123    7.9
124    6.8
125    3.6

Which RDBMS are you using? How do you want to group the accounts: throw away everything after the third character; throw away all non-numeric characters, somehow else? — Quassnoi
– Quassnoi, Commented Aug 18, 2010 at 15:04

Martin Smith · Accepted Answer · 2010-08-18 15:25:41Z

5

You don't say any particular dialect of SQL

SELECT LEFT(Acct#,3), SUM(Amount)
FROM yourTable
GROUP BY LEFT(Acct#,3)

Or to handle arbitrary length account numbers

SELECT
         CASE
                  WHEN Acct# LIKE '%T'
                  THEN SUBSTRING(Acct#,1,LEN(@Acct)-1)
                  ELSE Acct#
         END,
         SUM(Amount)
FROM     yourTable
GROUP BY
         CASE
                  WHEN Acct# LIKE '%T'
                  THEN SUBSTRING(Acct#,1,LEN(@Acct)-1)
                  ELSE Acct#
         END

Or a more generic approach that will handle arbitrary mappings might be to construct a mapping table that you can then join on. There is quite a lot of missing information here as to the rules that need to be applied!

SELECT d.b, SUM(yt.Amount)
FROM yourTable yt
join (
        SELECT '123' as a, '123' as b UNION ALL
        SELECT '123T' as a, '123' as b UNION ALL
        SELECT '124' as a, '124' as b UNION ALL
        SELECT '124T' as a, '124' as b UNION ALL
        SELECT '125' as a, '125' as b UNION ALL
        SELECT '125T' as a, '125' as b
) d ON d.a = yt.Acct#
GROUP BY d.b

edited Aug 18, 2010 at 15:25

answered Aug 18, 2010 at 15:04

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Everard Over a year ago

Note for the question author, will your account numbers always be three digits long?

StuartLC · Accepted Answer · 2010-08-18 15:09:59Z

1

You can also try

SELECT REPLACE([Acct#],'T', ''), SUM(Amount) 
FROM Acct
GROUP BY REPLACE([Acct#],'T', '')

Test Data

create table acct
([acct#] varchar(10),
amount decimal(10,2)
)

insert into acct([acct#], amount) values ('123', 3.4 )
insert into acct([acct#], amount) values ('123T', 4.5 )
insert into acct([acct#], amount) values ('124', 2.3)
insert into acct([acct#], amount) values ('124T', 4.5)
insert into acct([acct#], amount) values ('125', 1.2)
insert into acct([acct#], amount) values ('125T', 2.4)

answered Aug 18, 2010 at 15:09

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Comments

David C · Accepted Answer · 2010-08-18 15:29:01Z

1

I would have done that:

select b.acct#, (a.Amount + b.Amount) as Amount FROM yourTable as a inner join yourTable as b ON a.acct# = b.acct# + 'T'

answered Aug 18, 2010 at 15:29

David C

6686 silver badges12 bronze badges

1 Comment

Martin Smith Over a year ago

This accesses the table twice and assumes there will always be a corresponding T record to join on. +1 though.

Collectives™ on Stack Overflow

Help creating a SQL SELECT statement

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related