Count function in 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,T
69.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','Postp
oned'))
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,T
69.name,T1.submit_da
te
--,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','Postp
oned'))
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

When you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
included.

Based upon your numbers, it appears that 6 rows meeting the criteria have
NULL values for T1.dbid.

If you want the total number of rows, then count(1) or count(*)

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


<chandresh.x.soni@sonyericsson.com> wrote in message
news:1164868492.659827.20610@14g2000cws.googlegroups.com...
> 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,T
69.name,T1.submit_da
te,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','Postp
oned'))
> 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,T
69.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','Postp
oned'))
> 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
>

Hi Arnie,

First of all thanks for replying to my mail. but yr solution did not
work when i use count(*) or count(1) it returns 5282 as count

/soni

Arnie Rowland  wrote:[color=darkred
]
> When you do count(T1.dbid), any rows with NULL values for T1.dbid are NOT
> included.
>
> Based upon your numbers, it appears that 6 rows meeting the criteria have
> NULL values for T1.dbid.
>
> If you want the total number of rows, then count(1) or count(*)
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
>
> <chandresh.x.soni@sonyericsson.com> wrote in message
> news:1164868492.659827.20610@14g2000cws.googlegroups.com... 

what if you select count(distinct col) from table...

<chandresh.x.soni@sonyericsson.com> wrote in message
news:1164870965.270003.52360@80g2000cwy.googlegroups.com...
> Hi Arnie,
>
> First of all thanks for replying to my mail. but yr solution did not
> work when i use count(*) or count(1) it returns 5282 as count
>
> /soni
>
> Arnie Rowland wrote: 
>

The DISTINCT on the first query results in distinct ROWS.  The
count(distinct T1.dbid) counts distinct values of T1.dbid.  If you
inspect the data 2930 rows returned by the first query you will find
duplicates of T1.dbid, even with the DISTINCT.

The only way I know to count what you want counted is to place the
entire first query into a derived table, and count that.

SELECT count(*)
FROM (<insert first query here, minus ORDER BY> ) as X

Roy Harvey
Beacon Falls, CT

On 29 Nov 2006 22:34:52 -0800, chandresh.x.soni@sonyericsson.com
wrote:

>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,T
69.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','Postp
oned'))
>			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,T
69.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','Postp
oned'))
>			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

The first query specifies DISTINCT so duplicate *rows* are omitted and the
second only counts distinct non-null T1.dbid values.  I think the easiest
way to get the desired count is to wrap the original query in a derived
table.  Untested example:

SELECT COUNT(*)
FROM (
select distinct
T1.dbid,T1.id,T1.title,T3.name,T1. implemented_status,T
69.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','Postp
oned'))
and T1.submit_date > {ts '2006-03-25 14:59:59'}))
) AS results

--
Hope this helps.

Dan Guzman
SQL Server MVP

<chandresh.x.soni@sonyericsson.com> wrote in message
news:1164868492.659827.20610@14g2000cws.googlegroups.com...
> 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,T
69.name,T1.submit_da
te,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','Postp
oned'))
> 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,T
69.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','Postp
oned'))
> 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
>