Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select report query Expand / Collapse
Author
Message
Posted Wednesday, July 14, 2010 5:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 15, 2010 4:35 AM
Points: 18, Visits: 23
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
331 1036 123.0000 2 5.05776
331 1037 100.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.
Post #952193
Posted Wednesday, July 14, 2010 5:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 2,459, Visits: 7,951
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
*/




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn
Post #952214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse