Cannot determine Distinct records?

[NewbiDoobie]

I have a field that i am trying to select/noselect based on if there is mosre than 1 distinct record in the field,

I have 3 records

ID FIELD1
1 W/ oop
2 W/o oop
3 W/o oop


I am being told that there is only 1 distinct field1

Why will it not give me the correct count of 2 and is there a workaround for this?

 

[jbenson001]

Please paste your code.

 

[NewbiDoobie]

select @Qual3Count = count(distinct isnull(field1,'') ) from @MasterTable where ID = @ID

 

[amrita418]

Can you post the query you are running?

select count(distinct field1) from TableA

Regards,
AA

 

[NewbiDoobie]

Before it gets asked,

All 3 records are returned by a select statement for this @ID

All I need is the distinct count for these records. I am expecting 2, but am only getting 1

The table should look like: (Ishortened it for ease os typing)

ID FIELD1
1 W/ oop
1 W/o oop
1 W/o oop

 

[jbenson001]

You are getting one row because you are using a WHERE clause: where ID = @ID

Remove that to see all distinct rows.

 

[gmmastros]

It looks like your where clause would be filtering out all rows except 1, so of course there would only be 1 distinct value. At least, from your original post, it appears as though ID is a unique identifier.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

If your field1 is a char(x) and not a varchar(x) you may need to use to trim the column.

select @Qual3Count =  count(distinct isnull(LTrim(RTrim(field1)),'') ) from @MasterTable where ID = @ID

 

[NewbiDoobie]

SORRY, I mistyped it there is an additional ID in this table.

LET me start over:


Table looks like this
ID FIELD1
1 W/ oop
1 W/o oop
1 W/o oop

query is this:
select @Qual3Count = count(distinct isnull(field1,'') ) from @MasterTable where ID = 1

It is returning all 3 records when I
select field1 where ID = 1
however it is returning 1 for a count of distinct records

 

[gmmastros]

There must be something else going on with the data. I tested it like this...

Declare @MasterTable  Table(Id Integer, Field1 VarChar(10))

Insert Into @MasterTable  Values(1,'W/ oop')
Insert Into @MasterTable  Values(1,'W/o oop')
Insert Into @MasterTable  Values(1,'W/o oop')

Declare @Qual3Count Integer
Declare @Id Integer

Set @Id = 1

select @Qual3Count =  count(distinct isnull(field1,'') ) from @MasterTable  where ID = @ID

Select @Qual3Count

It showed the result as 2. Check your data again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NewbiDoobie]

The field is an nvarchar

 

[gmmastros]

Did you specify the size of the nvarchar when you created the table variable? If not, that would explain it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

Try this and post back the results:

select distinct '''' + field1 + '''' from @MasterTable where Id = @Id

Regards,
AA

 

[NewbiDoobie]

While I am at let me post the whole code: (It still does not work against my data)

declare @MasterTable table(AAIA_ID int
,Model_Name varchar(30)
,Nameplate varchar(30)
,Type varchar(10)
,Liters varchar(4)
,Quarts numeric(18,2)
,Filt varchar(1)
,Note varchar(3)
,Notes varchar(600)
)


insert into @MasterTable
select AAIA_ID
,Model_Name
,Nameplate
,Type
,Case When Liters = 'NA' Then '0' Else Liters End As Liters
, case When Liters = 'NA' Then 0 Else convert(numeric (18,2), Liters) * 1.0567 End as Quarts
,Filt
,a.note
,b.Descript



from
AccessSpecs.dbo.X_OILCAP a left outer join
NewNote b on a.note = b.note
where liters is not null and aaia_id = 1315263

order by AAIA_ID

select * from @MasterTable

-------------------------------------------------------------------------------------------
Declare @AAIA int
,@Model_Name varchar(30)
,@Nameplate varchar(30)
,@Type varchar(10)
,@Liters varchar(4)
,@Quarts numeric(18,2)
,@Filt varchar(1)
,@Note nvarchar(3)
,@Notes varchar(600)
,@HTMLStart varchar(8000)
,@HTMLEnd varchar(8000)
,@HTMLBody varchar(8000)
,@Qual1Count int
,@Qual2Count int
,@Qual3Count int

Declare @TableName varchar(150)
Set @TableName = 'Engine Oil Capacity'
Declare @copyright varchar(255)
set @copyright = '<TD align=Center Size-6 Colspan =3 nowrap\><FONT>Copyright &copy; 2005 by Motor Information Systems, a division of Hearst Publishing, Inc. All rights reserved.</font></TD>'

Declare @caption varchar(255)
Select @Caption = dbo.caption(@TableName)

Declare @header varchar(200)
set @header = '<html><head><title>' + @TableName + '</title></head><body>'



--print @caption
set @HTMLStart = @header + '<table>'+ @caption
set @HTMLEnd = '<tr>'+ @copyright + '</tr>' + '</table></body></html>'



--First Curson Get AAIA Id numbers and loop through table

declare AAIA cursor for select distinct AAIA_Id from @mastertable
open aaia
fetch next from AAIA into
@AAIA


while @@fetch_status = 0
begin
--Second Cursor

declare Processing1 cursor for Select * from @MasterTable where AAIA_ID = @AAIA
open Processing1

fetch next from Processing1 into
@AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes
select @Qual3Count = count(distinct isnull(@Type,'') ) from @MasterTable where AAIA_ID = @AAIA
select @Qual1Count = count(distinct isnull(@Model_Name,'')) from @MasterTable where AAIA_ID = @AAIA
select @Qual2Count = count(distinct isnull(@Nameplate,'')) from @MasterTable where AAIA_ID = @AAIA


while @@fetch_status = 0
begin
--------------------------------------------------------------------------------------------------------
select @Qual1Count, @Qual2Count, @Qual3Count

select @AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes



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

fetch next from Processing1 into
@AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes




end--End Second Loop
Deallocate Processing1
Close Processing1


fetch next from AAIA into
@AAIA


end -- End First loop
deallocate AAIA
close AAIA

 

[NewbiDoobie]

Amarita,

All i am getting is 'W/o oop'.... This was the same when i tried to select the distinct records.

Why is this?

 

[amrita418]

Looking at the big picture, can you post what you are trying to do in this procedure (requirement with some sample data and expected output?

 

[NewbiDoobie]

What I am trying to do is select a grouping of records and convert them to an HTML page.

However whether or not I show certain fields are based on whether or not there are multiple values in the fields.

The @type(field1) field is one of those.

Everything else works perfectly till I try to do a count on the distinct number of records in that field.

does this have something to do with the / as it affects the distinct count and the sort order?

 

[jbenson001]

As amrita418 said, if you post your procedure it would help us help you.

 

[NewbiDoobie]

Jbenson,

I will repost it since I thought it was there but noone else seems to see it:

the part that does not seem to work is: select @Qual3Count = count(distinct isnull(@Type,'') ) from @MasterTable where AAIA_ID = @AAIA


for this the base records would be:

1 W/ oop
1 W/o oop
1 W/o oop

I am expecting to see a 2 and I keep getting a 1


My procedure is:


declare @MasterTable table(AAIA_ID int
,Model_Name varchar(30)
,Nameplate varchar(30)
,Type varchar(10)
,Liters varchar(4)
,Quarts numeric(18,2)
,Filt varchar(1)
,Note varchar(3)
,Notes varchar(600)
)


insert into @MasterTable
select AAIA_ID
,Model_Name
,Nameplate
,Type
,Case When Liters = 'NA' Then '0' Else Liters End As Liters
, case When Liters = 'NA' Then 0 Else convert(numeric (18,2), Liters) * 1.0567 End as Quarts
,Filt
,a.note
,b.Descript



from
AccessSpecs.dbo.X_OILCAP a left outer join
NewNote b on a.note = b.note
where liters is not null and aaia_id = 1315263

order by AAIA_ID

select * from @MasterTable

-------------------------------------------------------------------------------------------
Declare @AAIA int
,@Model_Name varchar(30)
,@Nameplate varchar(30)
,@Type varchar(10)
,@Liters varchar(4)
,@Quarts numeric(18,2)
,@Filt varchar(1)
,@Note nvarchar(3)
,@Notes varchar(600)
,@HTMLStart varchar(8000)
,@HTMLEnd varchar(8000)
,@HTMLBody varchar(8000)
,@Qual1Count int
,@Qual2Count int
,@Qual3Count int

Declare @TableName varchar(150)
Set @TableName = 'Engine Oil Capacity'
Declare @copyright varchar(255)
set @copyright = '<TD align=Center Size-6 Colspan =3 nowrap\><FONT>Copyright &copy; 2005 by Motor Information Systems, a division of Hearst Publishing, Inc. All rights reserved.</font></TD>'

Declare @caption varchar(255)
Select @Caption = dbo.caption(@TableName)

Declare @header varchar(200)
set @header = '<html><head><title>' + @TableName + '</title></head><body>'



--print @caption
set @HTMLStart = @header + '<table>'+ @caption
set @HTMLEnd = '<tr>'+ @copyright + '</tr>' + '</table></body></html>'



--First Curson Get AAIA Id numbers and loop through table

declare AAIA cursor for select distinct AAIA_Id from @mastertable
open aaia
fetch next from AAIA into
@AAIA


while @@fetch_status = 0
begin
--Second Cursor

declare Processing1 cursor for Select * from @MasterTable where AAIA_ID = @AAIA
open Processing1

fetch next from Processing1 into
@AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes
select @Qual3Count = count(distinct isnull(@Type,'') ) from @MasterTable where AAIA_ID = @AAIA
select @Qual1Count = count(distinct isnull(@Model_Name,'')) from @MasterTable where AAIA_ID = @AAIA
select @Qual2Count = count(distinct isnull(@Nameplate,'')) from @MasterTable where AAIA_ID = @AAIA


while @@fetch_status = 0
begin
--------------------------------------------------------------------------------------------------------
select @Qual1Count, @Qual2Count, @Qual3Count

select @AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes



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

fetch next from Processing1 into
@AAIA
,@Model_Name
,@Nameplate
,@Type
,@Liters
,@Quarts
,@Filt
,@Note
,@Notes




end--End Second Loop
Deallocate Processing1
Close Processing1


fetch next from AAIA into
@AAIA


end -- End First loop
deallocate AAIA
close AAIA