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.

On error i want to send error description as mail

I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

You can create a table with the following fields packageid,packagename,taskname,errormessage.

And later write a stored procedure which will insert the data into this table from the package variables. On event handler tab call this stored procedure and pass the system variables "system::executioninstanceguid","system::packagename","system::sourcename","system::errordescription".

Once this is done you can read values from this particular table and assign it to a variable and later on call it in the send mail task.Hope this will help you.

|||

Use OnError Event in Event Handler and system variables such as [System::ErrorCode] and [System::ErrorDescription]

http://msdn2.microsoft.com/en-us/library/ms139744.aspx

|||

As you define above i'm doing such a way

dts.variable("User::vName").value = dts.variable("System::ErrorDescription")

but it is going me error in my script task.

:-(

please provide more specific detail or information.

|||

Hi..

1. In OnError Event Handler, Add a Script Task..

2. Define ReadWriteVariables value in ScriptTask Properties like this

ReadWriteVariables | User::vName,System::ErrorDescription

3. In VSA, edit your script like this..

Dts.Variables("vName").Value = Dts.Variables("ErrorDescription").Value.ToString

HTH

ADConsulting / SQLLeader.com / Daeseong Han

|||

Thanks for you needful help.

:-)

sql