Re: How do I write an SQL query to do this?

if Field1 and Field2 are the same in both records then: 1 A B 100
2 C D 75
1 A B 50

SELECT ID, FIELD1, FIELD2, SUM(PURCHASES) FROM TABLE
GROUP BY ID, FIELD1, FIELD2
1 A B 150
2 C D 75

IF field 1 or 2 differ between records IE 1 A X 100
2 C D 75
1 Z F 50

Then you have to decide what you are trying to sum and display. You may just need to have the ID
SELECT ID, SUM(PURCHASES)
FROM TABLE
GROUP BY ID
1 150
2 75

Or you can choose Field1 and Field 2
SELECT ID, MIN(FIELD1), MIN(FIELD2), SUM(PURCHASES) FROM TABLE
GROUP BY ID
will return
1 A F 150
2 C D 75

"D. Alvarado" <laredotornado_at_zipmail.com> wrote in message news:9fe1f2ad.0203141009.39d5adb2_at_posting.google.com...
> Hello,
> I'm pretty bad with SQL. However, I want to write a query that
> will sum all purchases for each purchaser. That is, given a simple
> table with the columns,
>
> ID FIELD1 FIELD2 ... PURCHASES
> -- ------ ------ ---------
> 1 xxx xxx 100
> 2 xxx xxx 75
> 1 xxx xxx 50
>
> I would like to obtain the results,
>
> ID FIELD1 FIELD2 ... SUM(PURCHASES)
> -- ------ ------ --------------
> 1 xxx xxx 150
> 2 xxx xxx 75
>
> It is fairly straightfoward to sum for a particular id, but I'm not
> familiar enough with the syntax for each id.
>
> Thanks for any help, Dave A.
Received on Thu Mar 14 2002 - 12:44:46 CST