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

Answer 1

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

Answer 2

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)

Answer 3

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'