4 Replies - 871 Views - Last Post: 28 April 2011 - 08:40 PM Rate Topic: -----

#1 yasmeen1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-April 11

Problem in creating SQL query

Posted 26 April 2011 - 08:02 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Problem in creating SQL query

#2 BrairMoss  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 15
  • View blog
  • Posts: 79
  • Joined: 13-April 10

Re: Problem in creating SQL query

Posted 27 April 2011 - 05:07 PM

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
Was This Post Helpful? 0
  • +
  • -

#3 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: Problem in creating SQL query

Posted 28 April 2011 - 05:21 PM

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.
Was This Post Helpful? 0
  • +
  • -

#4 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Problem in creating SQL query

Posted 28 April 2011 - 08:20 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 yasmeen1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 17-April 11

Re: Problem in creating SQL query

Posted 28 April 2011 - 08:40 PM

Thanks Guys...I solved my problem :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1