summing data and selecting into another table

[r0k3t]

Howdy,

Well... I know that I can select into another table easy enough. the problem is I need to sum() some data first. I have a Transaction table, and lets say ContactID '123456' is in there seven times, I want to some up all the transactions they have made them dump them into another table - so the records would look like

ContactId | transactionTotal
----------------------------
123456 | 45.00
----------------------------
235512 | 180.00

I have tried everything in my SQL power but I can't make it work, Below is the SQL I was using (of course I am not doing the select into part yet).

SELECT
SUM(TransactionTotal)
FROM
[Transaction]
WHERE
ContactId = (SELECT ContactId FROM [Transaction])

Any pointers would be great...

Thanks

[chazzy]

there's two approaches to selecting into another table. I prefer this approach.


INSERT into THAT_OTHER_TABLE
SELECT
SUM(TransactionTotal) as target_column_in_THAT_OTHER_TABLE
FROM
[Transaction]
WHERE
ContactId = (SELECT ContactId FROM [Transaction])

[russell]

i think it needs to look more like

INSERT INTO THAT_OTHER_TABLE
SELECT ContactId, sum(TransactionTotal)
FROM Transaction
GROUP BY
ContactId


but not sure exactly what r0k3t is after...

[r0k3t]

Thanks all,

What I ended up doing what this...

DELETE FROM TempTransactionTotals

INSERT INTO TempTransactionTotals
SELECT ContactId, sum(TransactionTotal)
FROM [Transaction]
WHERE ApprovalStatus = 1
GROUP BY
ContactId

I went that route cause it is easy to read and fairly straight forward, you can however do it without a temp table - that was my first thought but it gets so messy it'll make you crazy. This is what a working version of that option (no temp table) looks like for anyone who is interested.


select c.Id,
c.FirstName,
c.MiddleName,
c.LastName,
c.ProfessionalTitle,
c.Company,
c.Email,
c.CreatedOn,
c.Title,
c.InactiveForFraud,
c.Telephone,
c.Fax,
c.CompanyWebUrl,
c.MailingList,
n.Description,
h.Answer as HowDidYouHear,
u.Username,
R.ReferrerId,
ad1.Address1,
ad1.Address2,
ad1.City,
ad1.State,
ad1.PostalCode,
re.Shortname,
ad1.AddressTypeId,
ad2.Address1 as ShippingAddress1,
ad2.Address2 as ShippingAddress2,
ad2.City as ShippingCity,
ad2.State as ShippingState,
ad2.PostalCode as ShippingPostalCode,
re2.ShortName as ShippingCountry,
ad2.AddressTypeId as ShippinigAddressTypeId,
total, t3.contactid
FROM Contact AS c (NOLOCK)
LEFT JOIN Users AS u (NOLOCK) ON u.contactid = c.Id
LEFT JOIN Address AS ad1 (NOLOCK) ON ad1.contactid = c.Id
LEFT JOIN Referrer AS r (NOLOCK) ON c.referrerid = r.Id
LEFT JOIN NotificationType AS n (NOLOCK) ON n.id = c.NotificationTypeId
LEFT JOIN HowDidYouHear AS h (NOLOCK) ON h.id = c.howdidyouhearid
LEFT JOIN Region AS re (NOLOCK) ON ad1.countryregionid = re.Id
LEFT JOIN (SELECT * FROM Address ad2 (NOLOCK) WHERE AddressTypeId='S') AS ad2 ON ad1.ContactId=ad2.ContactId
LEFT JOIN Region AS re2 (NOLOCK) ON ad2.CountryRegionId = re2.Id
left join (select sum(t2.transactiontotal) as total, t2.contactid from [transaction] t2 group by contactid)
as t3 on t3.contactid=c.id
WHERE ad1.AddressTypeId='B'



- Yuk! Doesn't it just get messy? BUT! no temp table required.