[yasmeen1]

HI, I am creating an application for my client in which I have to accomplish many thing in 1 query.

I have only 1 table with multiple columns, Thinks I need to do are:
1. I have to display sum of 'quantity' based on the KEY, Submitter, and action fields
2. I have to pick 'price' value and if the 'action' for that price is 'BUY' then I have to add it in Buy column and If action is sell, then It will go to Sell column. It should be arranged according to KEY.
3. I filter the values based on submitter name and date chosen by the user.

Here My problem is:
1. My query calculate Sum for Quantity not based on the submitter and action. It is not giving me correct figure.
2. In both columns of sell and Buy, values are coming same. But it should be different.


Here is my Code:

SELECT distinct SUM(a1.Quantity) as Quantity, a1.[Key],a1.[Date],a1.[Symbol], a1.[Action], a1.[Price],a2.[Price] as SOLD, a1.submitter, ((a2.[Price] - a1.Price) * SUM(a1.Quantity))/ a2.[Price] as Result FROM trade_log a1, trade_log a2 WHERE (a1.submitter='" + DropDownList1.SelectedValue + "') and (a1.Date >= '" + TextBox1.Text + "' and a1.Date <='" + TextBox2.Text + "') and (a1.[Key]=a2.[Key]) and (a1.[Action] = 'BOT' or a2.[Action] = 'SLD') and a1.[Submitter] IS NOT NULL group by a1.[Key],a1.[Date],a1.Symbol,a1.[Action],a1.[Price],a2.[Price],a1.submitter

[BrairMoss]

yasmeen1,

I do believe you would get a better response if you were to post this in the SQL forums. If this thread gets moved, or you repost it in the correct section I would be happy to help.

//BrairMoss

[SurfingShark]

It would also be easier if you formatted your SQL query in a more readable and SQL-type look to it. It is treachurous trying to read that. Looks like it was auto-generated from some UI.

[Nakor]

also putting control values directly in your sql statement = very bad idea, unless you're just wanting your site to be open to sql injection attacks. You should be using SqlParameters.

[yasmeen1]

Thanks Guys...I solved my problem