Sum function in sql2005 Query

[Leesha]

Hi,
I'm working on a query for a web page that uses sqlServer2005. I need to produce the sum of fields by username and wedding name. The query runs fine other than I can't get the sql correct to produce the sum. I'm providing the sql as is an would appreciate guidance as to how to obtain the sum of [4x6], [5x7] and [8x10]

SELECT UserName, Cost1 AS [4x6], Cost2 AS [5x7], Cost3 AS [8x10], WeddingName
FROM tblPhotoOrders
WHERE (UserName = @UserName) AND (WeddingName = 'JoeyLee')

Thanks!
Leesha

[HansV]

Try

SELECT UserName, Sum(Cost1) AS [4x6], Sum(Cost2) AS [5x7], Sum(Cost3) AS [8x10], WeddingName
FROM tblPhotoOrders
WHERE (UserName = @UserName) AND (WeddingName = 'JoeyLee')
GROUP BY UserName, WeddingName

[Leesha]

Hi Hans!

That did the trick. One more question, how would I format it as currency?

Thanks!
Leesha

[HansV]

Try

SELECT UserName, Convert(VarChar, Convert(Money, Sum(Cost1)), 1) AS [4x6], Convert(VarChar, Convert(Money, Sum(Cost2)), 1) AS [5x7], Convert(VarChar, Convert(Money, Sum(Cost3)), 1) AS [8x10], WeddingName
FROM tblPhotoOrders
WHERE (UserName = @UserName) AND (WeddingName = 'JoeyLee')
GROUP BY UserName, WeddingName