MS SQL Server - Count function in Query

Author

Count function in Query

chandresh.x.soni@sonyericsson.com

2006-11-30, 5:17 am

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_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

Arnie Rowland

2006-11-30, 5:17 am

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_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_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
>

chandresh.x.soni@sonyericsson.com

2006-11-30, 5:17 am

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 the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> <chandresh.x.soni@sonyericsson.com> wrote in message
> news:1164868492.659827.20610@14g2000cws.googlegroups.com...

Uri Dimant

2006-11-30, 5:17 am

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:
>

Roy Harvey

2006-11-30, 7:14 pm

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_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_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

Dan Guzman

2006-11-30, 7:14 pm

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_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_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
>

Sponsored Links