I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):
set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Com bined_4fields](@BondGroupID int)
AS SELECT DISTINCT
SUM(dbo.Bond.Lo anAmount) AS SumOfBondValue, COUNT(dbo.Bond. BondID) AS CountOfBond, dbo.ProgressSte pType.ProgressS tepType
FROM dbo.BondGroup INNER JOIN
dbo.Transaction Table ON dbo.BondGroup.B ondGroupID = dbo.Transaction Table.BondGroup ID INNER JOIN
dbo.Bond ON dbo.Transaction Table.Transacti onID = dbo.Bond.Transa ctionID INNER JOIN
dbo.ProgressSte p ON dbo.Transaction Table.Transacti onID = dbo.ProgressSte p.TransactionID INNER JOIN
dbo.ProgressSte pType ON dbo.ProgressSte p.ProgressStepT ypeID = dbo.ProgressSte pType.ProgressS tepTypeID
WHERE (dbo.BondGroup. BondGroupID = @BondGroupID)
GROUP BY dbo.ProgressSte pType.ProgressS tepType, dbo.ProgressSte p.ProgressStepI D
HAVING (dbo.ProgressSt epType.Progress StepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressSt epType.Progress StepType = 'Grants') OR
(dbo.ProgressSt epType.Progress StepType = 'Attorney') OR
(dbo.ProgressSt epType.Progress StepType = 'Feedback received from bank')
ORDER BY dbo.ProgressSte pType.ProgressS tepType
*********
With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressSte pType.ProgressS tepType the query should only read it once. In other words the dbo.ProgressSte pType.ProgressS tepType is a unique value and the rest not...
Please help, my boss is on my case :)
set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Com bined_4fields](@BondGroupID int)
AS SELECT DISTINCT
SUM(dbo.Bond.Lo anAmount) AS SumOfBondValue, COUNT(dbo.Bond. BondID) AS CountOfBond, dbo.ProgressSte pType.ProgressS tepType
FROM dbo.BondGroup INNER JOIN
dbo.Transaction Table ON dbo.BondGroup.B ondGroupID = dbo.Transaction Table.BondGroup ID INNER JOIN
dbo.Bond ON dbo.Transaction Table.Transacti onID = dbo.Bond.Transa ctionID INNER JOIN
dbo.ProgressSte p ON dbo.Transaction Table.Transacti onID = dbo.ProgressSte p.TransactionID INNER JOIN
dbo.ProgressSte pType ON dbo.ProgressSte p.ProgressStepT ypeID = dbo.ProgressSte pType.ProgressS tepTypeID
WHERE (dbo.BondGroup. BondGroupID = @BondGroupID)
GROUP BY dbo.ProgressSte pType.ProgressS tepType, dbo.ProgressSte p.ProgressStepI D
HAVING (dbo.ProgressSt epType.Progress StepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressSt epType.Progress StepType = 'Grants') OR
(dbo.ProgressSt epType.Progress StepType = 'Attorney') OR
(dbo.ProgressSt epType.Progress StepType = 'Feedback received from bank')
ORDER BY dbo.ProgressSte pType.ProgressS tepType
*********
With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressSte pType.ProgressS tepType the query should only read it once. In other words the dbo.ProgressSte pType.ProgressS tepType is a unique value and the rest not...
Please help, my boss is on my case :)