One Account and one product result

As an example i have 4 columns:

Account, Product, Description, Price

I want to retrieve every account that has bought a particular product and sum up the price - I managed to do that but it lists it as:

Account, Product, Description, Price

Acc1 CD Compact Disc 50

Acc2 CD Compact Disc 50

Acc3 CD Compact Disc 50

Acc4 CD Compact Disc 50

So gives me a total for each account. I want to have the above returned back as ALL the accounts i have chosen but only list the product once with the price ie.:

Account, Product, Description, Price

Acc1,2,3,4 CD Compact Disc 200

How could this be done?

Thanks

Since you didn't provide the table DDL, I will work with only the information you supplied.

Code Snippet

SELECT
Account = substring(( SELECT ', ' + Account as [text()]
FROM MyTable m2
WHERE m2.Account = m1.Account
FOR XML path(''), elements
), 3, 1000
),
Product,
Description,
Price = sum( Price)
FROM MyTable m1
GROUP BY Product

|||

EssCee:

I am bothered by what I see in your result set:

Account Product Description Price

- -- -- --

Acc1,2,3,4 CD Compact Disc 200

What bothers me most is seing the ACCOUNT code listed as "Acc1,2,3,4". Are you really wanting the query to break the ACCOUNT column apart to get a "Base Account" component and then append a "string product" of all of the numeric pieces of all of the different values in the ACCOUNT column?

Also, I am baffeled by seeing the results of PRICE as the sum of the PRICE columns of each row. Would you also please verify if this is correct?

|||

Kent,

I didn't see your response as a 'rant', only asking deeper questions.

I didn't see the account grouping issue you raised -it wasn't apparant in the OP. The sum() issue seemed specious, but there are many, many 'strange' uses of data around. What gets me is how often we get requests to concatenate all column values into one result. I guess most folks just don't get it that presentation is a client task, not a data server task.

With the growth in SQL Server, and the apparant 'push' to move data code down to the application, we will be getting more and more 'half-backed' designs (EssCee, I don't know enough of your design to make that assumption, please don't take it personally.) Say after me: "Developers are not Database Professionals, They are trying to solve a user related problem. Their prime objective is to make the end user happy. A DBA's prime objective is to protect the data at all costs." Repeat as often as necessary -THEN bang you head on your desk.

|||

I know it doesn't answer your question directly...but....

Probably you could get by with a COUNT of the number of accounts that have purchased that product:

Code Snippet

select Count(*) as [AccountCount]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

This will get you exactly what you asked for, but it's two hits to the table:

Code Snippet

declare @.AccountList nvarchar(4000)

select @.AccountList = isnull(@.AccountList + ',' + Account,Account)

from <MyTable>

where Product = 'CD'

select @.AccountList as [Accounts]

, Product

, Description

, sum(Price) as [GrossRevenue]

from <MyTable>

where Product = 'CD'

group by Product, Description

|||

Thanks everyone for your input. Just to clarify

The database is not designed by me. Another company who dont provide this support but im trying to develop a solution for some of these problems. Reason i need it in this format is because two companies have merged together and i need one total - hopefully that makes sense.

Ill try out all of these suggestions. Thanks to you all.