count function in a query

Hello SQL Experts,

I have a Query which is giving me correct result. but i was interested
in getting just the count of the Issues it returns.

--
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_dat-e,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
--

If i run the query it gives me 2930 rows but if i change the query to
return only rows using Count() function then i get wrong results some
2924 Rows

--
select count(distinct T1.dbid)
--T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_d-ate
--,T1.abc_rank
from ( ( ( ( ( issue T1
INNER JOIN statedef T3 ON T1.state = T3.id )
INNER JOIN project T2 ON T1.project = T2.dbid )
LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid =
T48mm.parent_dbid
and 16780481 = T48mm.parent_fielddef_id )
LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )
LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )
where T1.dbid <> 0
and ((T1.issue_type = 'Defect'
and T2.name = 'SW Application Platform Wilma'
and (((T1.implemented_status <> 'Not started' or
T1.implemented_status is NULL)
and T3.name in ('Assigned'))
or (T69.name = '''NULL''' and T3.name in ('Verified'))
or T3.name in ('Integrated','Postponed'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
order by T1.id ASC
--

any ways to improve this as i need to use to capture the value in a
varibale and store in some other table.

/Soni

i can realize that your table have some duplicate rows for T1.dbid

Any one of these columns may have duplicate value against to T1.dbid. T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_dat-e,T1.abc_rank

If you change your first query as

select distinct
T1.dbid from ( ( ( ( ( issue T1 ....

You will get only 2924 rows.

or

if you change your second query as

select count(distinct T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_d-ate ,T1.abc_rank) from ....

You will get only 2930 rows.

|||

Hi MainD

I tried yr way

Select count(distinct T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_date,T1.abc_rank)

from ( ( ( ( ( issue T1

INNER JOIN statedef T3 ON T1.state = T3.id )

INNER JOIN project T2 ON T1.project = T2.dbid )

LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid = T48mm.parent_dbid

and 16780481 = T48mm.parent_fielddef_id )

LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )

LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )

where T1.dbid <> 0

and ((T1.issue_type = 'Defect'

and T2.name = 'SW Application Platform Wilma'

and (((T1.implemented_status <> 'Not started' or T1.implemented_status is NULL)

and T3.name in ('Assigned'))

or (T69.name = '''NULL''' and T3.name in ('Verified'))

or T3.name in ('Integrated','Postponed'))

and T1.submit_date > {ts '2006-03-25 14:59:59'}))

order by T1.id ASC

but gets an error message

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near ','.

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near 'issue'.

Msg 170, Level 15, State 1, Line 13

Line 13: Incorrect syntax near 'T1'.

but thanks for helping me.

|||

sorry .. change the query as follow as

Select distinct count(*) from ....

|||no 5282 as count :(|||

To find your duplicate row use the following query..

Select dbid,Count(dbid) From (Select Distinct T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_date,T1.abc_rank

from ( ( ( ( ( issue T1

INNER JOIN statedef T3 ON T1.state = T3.id )

INNER JOIN project T2 ON T1.project = T2.dbid )

LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid = T48mm.parent_dbid

and 16780481 = T48mm.parent_fielddef_id )

LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )

LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )

where T1.dbid <> 0

and ((T1.issue_type = 'Defect'

and T2.name = 'SW Application Platform Wilma'

and (((T1.implemented_status <> 'Not started' or T1.implemented_status is NULL)

and T3.name in ('Assigned'))

or (T69.name = '''NULL''' and T3.name in ('Verified'))

or T3.name in ('Integrated','Postponed'))

and T1.submit_date > {ts '2006-03-25 14:59:59'}))

) as Data group by Dbid Having Count(dbid) > 1

|||

yes.. bcs we used count(*) try this query now you will get 2930 rows

Select Count(*) From (Select Distinct T1.dbid,T1.id,T1.title,T3.name,T1.implemented_status,T69.name,T1.submit_date,T1.abc_rank

from ( ( ( ( ( issue T1

INNER JOIN statedef T3 ON T1.state = T3.id )

INNER JOIN project T2 ON T1.project = T2.dbid )

LEFT OUTER JOIN parent_child_links T48mm ON T1.dbid = T48mm.parent_dbid

and 16780481 = T48mm.parent_fielddef_id )

LEFT OUTER JOIN testrecord T48 ON T48mm.child_dbid = T48.dbid )

LEFT OUTER JOIN sw_label T69 ON T48.available_in_version = T69.dbid )

where T1.dbid <> 0

and ((T1.issue_type = 'Defect'

and T2.name = 'SW Application Platform Wilma'

and (((T1.implemented_status <> 'Not started' or T1.implemented_status is NULL)

and T3.name in ('Assigned'))

or (T69.name = '''NULL''' and T3.name in ('Verified'))

or T3.name in ('Integrated','Postponed'))

and T1.submit_date > {ts '2006-03-25 14:59:59'}))

) as Data

|||

Hi Chandresh,

You cant do as suggested by MainD

Try this, will throw the same error

Select count(distinct a,b) from x;

the problem is your data in the tables, it seem you are having duplicate data in the tables(as suggested by the MainD). try to find that first

or

Try "divide and conquer " approch, i mean break your query in small parts & check the results.

or send us the sample data where you got the wrong results.

Gurpreet S. Gill

|||

Hi,

The difference between both the queries, The first query is looking discinct value of all columns specified so that it will filter out each record having unique values for all coulmns. where as the second query is just looking the distinct value of count(distinct T1.dbid) which might have a duplicate or Null value.

look in to data and figure it out.