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

Article View: microsoft.public.msaccess.chat

Article #1

Back to microsoft.public.msaccess.chat Previous Next

From: 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!

Technical Details

Message-ID: <rvft69ktmom67@corp.supernews.com>

Path: arc1-ca.pugleaf.net!unknown!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 115df1,b17fd4d87b5087ba,start
X-Google-Attributes: gid115df1,public
X-Google-Thread: 10de66,b17fd4d87b5087ba,start
X-Google-Attributes: gid10de66,public
X-Google-Thread: 11301e,b17fd4d87b5087ba,start
X-Google-Attributes: gid11301e,public
X-Google-Thread: 107d2f,b17fd4d87b5087ba,start
X-Google-Attributes: gid107d2f,public
X-Google-Thread: ffa7b,b17fd4d87b5087ba,start
X-Google-Attributes: gidffa7b,public
From: "Brad" <brad@xspace.com>
Subject: Challange: How do I sum data from the same table in an SQL query???
Date: 1999/10/03
Message-ID: <rvft69ktmom67@corp.supernews.com>
X-Deja-AN: 532469215
Organization: Posted via Supernews, http://www.supernews.com
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Newsgroups: microsoft.public.msaccess.chat,borland.public.delphi.database.sqlservers,microsoft.public.sqlserver,microsoft.public.sqlserver.odbc,microsoft.public.sqlserver.programming
X-Complaints-To: newsabuse@supernews.com