SQL DISTINCT UNIQUE QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DISTINCT UNIQUE QUERY

Author

Topic

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 03:37:56

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_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int)
AS SELECT DISTINCT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID
WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepID
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

*********
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.ProgressStepType.ProgressStepType the query should only read it once. In other words the dbo.ProgressStepType.ProgressStepType is a unique value and the rest not...

Please help, my boss is on my case :)

bpgupta
Yak Posting Veteran

75 Posts

Posted - 2007-05-04 : 03:47:16

Please remove the dbo.ProgressStep.ProgressStepID from group by clause and then see the result.

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 04:11:46

Just tried removing the dbo.ProgressStep.ProgressStepID from group by clause. My results are very wrong then, i get only one row in result and am supposed to get something like 20+ , The ProgressStepID is the ID of the ProgressStepType, so its needed there i think...

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 05:12:18

please post some sample data and required output u want to get quick reply...

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 05:39:02

Here is some sample data:

Bond.LoanAmount Bond.BondId dbo.ProgressStepType.ProgressStepType
---------------------------------------------------------------------
654000 5 Attorney
654000 5 Grant
654000 5 Attorney
654000 15 Attorney
654000 15 Feed
654000 15 Feed
340000 21 Attorney
340000 21 Grant

The query should give the following results:

Bond.LoanAmount Bond.BondId dbo.ProgressStepType.ProgressStepType
---------------------------------------------------------------------
654000 5 Attorney
654000 5 Grant

654000 15 Attorney
654000 15 Feed

340000 21 Attorney
340000 21 Grant

But at the moment (With the Distinct) it gives:

Bond.LoanAmount Bond.BondId dbo.ProgressStepType.ProgressStepType
---------------------------------------------------------------------
654000 5 Attorney
654000 5 Grant

654000 15 Feed

340000 21 Attorney
340000 21 Grant

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 05:46:14

declare @tt table (col1 int, col2 int, col3 varchar(50))
insert @tt
select 654000, 5, 'Attorney' union all
select 654000, 5, 'Grant' union all
select 654000, 5, 'Attorney' union all
select 654000, 15, 'Attorney' union all
select 654000, 15, 'Feed' union all
select 654000, 15, 'Feed' union all
select 340000, 21, 'Attorney' union all
select 340000, 21, 'Grant'

select col1, col2, col3 from @tt group by col1, col2, col3 order by col2

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 05:57:08

Using DISTINCT, it is giving me desired output:

Select Distinct LoanAmount, BondId, ProgressStepType
from Table
order by 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 06:01:36

I tried changing the procedure to the below, but i seem to get a null resultset, am i maby doing something wrong with the procedure structure? I call the procedure to generate the resultset...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int)
AS
declare @tt table (col1 int, col2 int, col3 varchar(50))
insert @tt
SELECT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID
WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepTypeID
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

select col1, col2, col3 from @tt group by col1, col2, col3 order by col2

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 06:09:27

ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int)
AS
declare @tt table (col1 int, col2 int, col3 varchar(50))
insert @tt
SELECT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON (dbo.BondGroup.BondGroupID = @BondGroupID) and dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepTypeID
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 06:10:04

quote:
Originally posted by harsh_athalye

Using DISTINCT, it is giving me desired output:

Select Distinct LoanAmount, BondId, ProgressStepType
from Table
order by 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Doing that gives me the wrong resultset as well, if i have duplicate values of LoanAmount in more than one BondID, with the same ProgessStepType, it gives me only one and not both

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 06:14:33

bpguy, if i make the changes in your last post i get zeros as a result, but not null values...

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 06:23:54

quote:
Originally posted by NeoGeo

bpguy, if i make the changes in your last post i get zeros as a result, but not null values...

Sorry my bad, i dont get zeros, but instead of getting allot of rows it stacks all into four rows grouped with the ProgressType, like this:

LoanAmount CountOfBond Progerssteptype
4673000.00 7 AIP (Approval in Principle) received
3255000.00 5 Attorney
1947000.00 3 Feedback received from bank
9341000.00 14 Grants

But i need it in this form:

684000.00 1 AIP (Approval in Principle) received
694000.00 1 AIP (Approval in Principle) received
714000.00 1 AIP (Approval in Principle) received
644000.00 1 Attorney
649000.00 1 Attorney
654000.00 1 Attorney
649000.00 1 Feedback received from bank
634000.00 1 Grants
644000.00 1 Grants

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 06:41:52

try putting SumOfBondValue in the group by clause in ur origal query..like

SELECT
SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType
FROM dbo.BondGroup INNER JOIN
dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN
dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN
dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN
dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID
WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)

GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepTypeID, SumOfBondValue
HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR
(dbo.ProgressStepType.ProgressStepType = 'Grants') OR
(dbo.ProgressStepType.ProgressStepType = 'Attorney') OR
(dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank')
ORDER BY dbo.ProgressStepType.ProgressStepType

NeoGeo
Starting Member

9 Posts

Posted - 2007-05-04 : 06:46:50

Still no change in resultset

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 06:50:38

please post some sample data for each table (u have only 4 tables) and ur desired output...it is difficult for us to guess the output..please post some data for each table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources