getting 1 line from inner join returning 4 lines

[toon10]

Hi

I’m using this SQL query in 2005.

USE [MIS]
SELECT DasCompQty, DarQty, DarRWC,
DasFlnRate / 60 * DarRwTime * DarQty AS FaultCost
FROM dbo.AssemblyOperation AS [AO]
INNER JOIN dbo.[Assembly] AS [A] ON AO.AopAssNo = A.AssPartNo
INNER JOIN dbo.ListFlowline AS [LF] ON A.AssFlnId = LF.FlnId
INNER JOIN dbo.ListSection AS [LS] ON LF.FlnSecId = LS.SecId
INNER JOIN dbo.DailyAssOppComp AS [DC] ON A.AssPartNo = DC.DasAssNo 
      AND AO.AopOppNo = DC.DasOppNo
LEFT OUTER JOIN dbo.DailyAssOppRwk AS [DR] ON DC.DasAssNo = DR.DarAssNo
      AND DC.DasDate = DR.DarDate
      AND DC.DasOppNo = DR.DarOppNo
WHERE DC.DasDate >= '2009-01-09'
AND DC.DasDate <= '2009-01-09'
AND LF.FlnId LIKE '011'
AND AopOppNo LIKE '30'
AND DasPartNo LIKE '3351326'

It shows me 4 lines because of the inner join (there are 4 different DarRWC codes)

DasCompQty DarQty DarRWC FaultCost
50 1 RWC130 0.390533
50 1 RWC205 0.335913
50 1 RWC206 0.734639
50 1 RWC207 0.7018669

I am trying to get 1 line for a summary report I’m trying to write so I want the data to look like

DasCompQty DarQty FaultCost
50 4 2.1629519


Is there an easy way to achieve this or do I have to look into creating a temporary table?

Thanks

 

[bborissov]

SELECT DasCompQty,
      SUM(DarQty) AS DarQty,
      SUM(DasFlnRate / 60 * DarRwTime * DarQty) AS FaultCost
FROM dbo.AssemblyOperation AS [AO]
INNER JOIN dbo.[Assembly] AS [A] ON AO.AopAssNo = A.AssPartNo
INNER JOIN dbo.ListFlowline AS [LF] ON A.AssFlnId = LF.FlnId
INNER JOIN dbo.ListSection AS [LS] ON LF.FlnSecId = LS.SecId
INNER JOIN dbo.DailyAssOppComp AS [DC] ON A.AssPartNo = DC.DasAssNo
      AND AO.AopOppNo = DC.DasOppNo
LEFT OUTER JOIN dbo.DailyAssOppRwk AS [DR] ON DC.DasAssNo = DR.DarAssNo
      AND DC.DasDate = DR.DarDate
      AND DC.DasOppNo = DR.DarOppNo
WHERE DC.DasDate >= '2009-01-09'
AND DC.DasDate <= '2009-01-09'
AND LF.FlnId LIKE '011'
AND AopOppNo LIKE '30'
AND DasPartNo LIKE '3351326'
GROUP BY DasCompQty

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[toon10]

That's great. Thanks for your quick and accurate help! Much appreciated.

I had thought of using SUM but I had my syntax a little confused and i didn't work. Your solution works great.