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_stat us,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
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_stat us,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
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.googlegrou ps.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_stat us,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
> --
> 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_stat us,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
> --
> 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:[vbcol=seagreen]
> 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.googlegrou ps.com...
|||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_sta tus,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
>--
>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_stat us,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
>--
>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_stat us,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 results
Hope this helps.
Dan Guzman
SQL Server MVP
<chandresh.x.soni@.sonyericsson.com> wrote in message
news:1164868492.659827.20610@.14g2000cws.googlegrou ps.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_stat us,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
> --
> 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_stat us,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
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>