Select report query

• p.ravirao

  Old Hand

  Points: 380

  More actions

  July 14, 2010 at 5:26 am

  #221220

  I have written below query to get the total of each indicual items with same RequestID(requestitemsID are different)

  select

  (ts_a.CostLocal * ts_a.Qty)*(ts_b.ExchangeRate)) as Total_Dollor from ts_a ,ts_b

  Ex:

  RequestID RequestItemID CostLocal Qty Total_doller

  3311036123.0000 2 5.05776

  3311037100.0000 3 6.168

  Now i want get the Total of all requestitems those who having same RequestID.

  RequestID Total

  331 11.177

  Can anyone help to write query for the situation?

  Thanks.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  July 14, 2010 at 5:46 am

  #1192697

  Assuming I've understood you correctly, this should be what you want.

  --Once again, I'll build your test data first

  DECLARE @TABLE AS TABLE(

  [RequestID] INT,

  [RequestItemID] INT,

  [CostLocal] FLOAT,

  [Qty] INT,

  [Total_doller] FLOAT)

  INSERT INTO @TABLE([RequestID],[RequestItemID],[CostLocal],[Qty],[Total_doller])

  SELECT 331, 1036, 123.0000, 2, 5.05776

  UNION ALL SELECT 331, 1037, 100.0000, 3, 6.168

  --Now to the query

  SELECT [RequestID],

  SUM([Total_doller]) AS [Total]

  FROM @TABLE

  GROUP BY [RequestID]

  /*

  RequestID Total

  ----------- ----------------------

  331 11.22576

  */

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---