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
>

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,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_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
--
any ways to improve this as i need to use to capture the value in a
varibale and store in some other table.
/SoniWhen 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,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_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
> --
> 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:
> 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,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_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
> > --
> >
> > any ways to improve this as i need to use to capture the value in a
> > varibale and store in some other table.
> >
> > /Soni
> >|||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:
>> 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,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_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
>> > --
>> >
>> > 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 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,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_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
>--
>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,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.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,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_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
> --
> any ways to improve this as i need to use to capture the value in a
> varibale and store in some other table.
> /Soni
>

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,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_status,T69.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','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.
/SoniWhen 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,T69.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','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_
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 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,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|||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,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.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,T69.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','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_
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
>

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.

could anybody explain to me why sqldatadpater does not allow subqueries?

Dear experts,

Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS.

Well, actually i was using IN.

I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed??

Really appreciate it if some expert can tell me.

Thanks in advance

Hi Paul,

Can you post the source code here? I have used subqueries (using IN) and they have worked for me.

Girish