complicated query

[weazy]

I am at loggerheads with this SQL Query.

SELECT contact_id, name, address, amount, contrib_date, type FROM SillyHistory WHERE type_id = 1

this obviously returns all fields stated for records that have a type_id = 1, however, when I am printing the results I need to print to a table like:

name | address | amount | amount total | contrib_date

if there are several entries with the same contact_id, that means its the same person and therefore all their entries must be posted to the same row. I also need run a second query when there are multiple entries under the same contact_id to sum the amount field and print it in amount total.

the likelihood of multiple entries for the same contact_id is about 50/50 which is a lot when there are a couple thousand records .....

any help would be greatly appreciated.

[tomhath]

I assume some of the other columns are going to be different. So use row aggregates to pick which one you want to display for any that can vary:

SELECT contact_id, 
  min(name) as name, 
  min(address) as address, 
  sum(amount) as amounttotal, 
  min(contrib_date) as contrib_date, 
  min(type) as type 
FROM SillyHistory 
WHERE type_id = 1
group by contact_id