Challange: How do I sum data from the same table in an SQL query???

Thread View: microsoft.public.msaccess.chat

2 messages

[T] Back to Threads [V] Tree View [G] Group Articles

2 total messages Started by Brad Sun, 03 Oct 1999 00:00

Author: Brad
Date: Sun, 03 Oct 1999 00:00

35 lines
1931 bytes

Hmm. I thought this would be easy.

In Microsoft Access I have a set of data that looks like this:

AccountNum        Amount
2000A                    12
2000B                    3
4000A                    50
4000B                    40

I would like to 'add-up' accounts of the same number (eg: combine the "A"
accounts with the "B" accounts) in an SQL query. For example, the resulting
table would look like:

NewAccountNum        NewAmount
2000                            15
4000                             90

I've been trying to use a sub query that goes seomthing like:

SELECT
Amount +
    (SELECT Amount FROM table WHERE AccountNum = Left
    (AccountNum,LEN(AccountNum)-1) & "B")

AS NewAmount

FROM table

WHERE RIGHT(AccountNum,1) <> "B"

But of course my sub-query returns multple values.
If you have any suggestions, please reply directly to brad@xspace.com

Thanks!

Re: Challange: How do I sum data from the same table in an SQL query???

Author: John Peterson
Date: Sun, 03 Oct 1999 00:00

57 lines
2416 bytes

Brad,

You might try something like:

select substring(AccountNum, 1, datalength(AccountNum) - 1),
       sum(Amount)
  from YourTable
 group by substring(AccountNum, 1, datalength(AccountNum) - 1)

(BTW:  The substring might be overkill, but I wasn't sure how your
AccountNum data was structured.)

Hope this helps!

John Peterson

Brad <brad@xspace.com> wrote in message
news:rvft69ktmom67@corp.supernews.com...
> Hmm. I thought this would be easy.
>
> In Microsoft Access I have a set of data that looks like this:
>
> AccountNum        Amount
> 2000A                    12
> 2000B                    3
> 4000A                    50
> 4000B                    40
>
> I would like to 'add-up' accounts of the same number (eg: combine the "A"
> accounts with the "B" accounts) in an SQL query. For example, the
resulting
> table would look like:
>
> NewAccountNum        NewAmount
> 2000                            15
> 4000                             90
>
> I've been trying to use a sub query that goes seomthing like:
>
> SELECT
> Amount +
>     (SELECT Amount FROM table WHERE AccountNum = Left
>     (AccountNum,LEN(AccountNum)-1) & "B")
>
> AS NewAmount
>
> FROM table
>
> WHERE RIGHT(AccountNum,1) <> "B"
>
> But of course my sub-query returns multple values.
> If you have any suggestions, please reply directly to brad@xspace.com
>
> Thanks!
>
>
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads