Count Records in sqlcommand or sqladapter?

Hi everyone? I have a small problem here, I want to count the records found under the following method:

Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function

I want to see if this can be done and passed to the actual dataset? Is this possible? Or do I need to pass the found results from my query to a sqlreader? Also, Im I forced to put my colums together in a DataTable before actually binding to the DataSet?

 Thanks everyone! 

If I understand what you are looking for, you want the count of the number of records returned in your Dataset. All you have to do for that is:

dsVendorInfo.Tables(0).Rows.Count

|||Right, and how can I attach that to my dataset? Create a new column within the dataset that contains that value?|||

Why would you want to attach that to your dataset?? Anywhere you use the dataset you can retrieve the count.

|||

Scratch that. My intent is to simply pass the record count to a label. I am using an ajax control to extract data from this function and then the dataset populates all these labels in my form. I have a record count label which I have to also populate. So I was just thinking to add an extra column with a record count value but seems overhead to me. I created a new method that will just be used to populate that one label. My problem is that the record count does not show on your suggested "dsVendorInfo.Tables(0).Rows.Count" formula. I placed a break point on this line and its not being populated andI know its passing values (I am running a trace on the query). Here is my code:

<AjaxPro.AjaxMethod()> _Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Dim intCountAs Integer intCount = dsVendorInfo.Tables(0).Rows.Count 'showing empty when called upon RecordCount(intCount)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function Private Sub RecordCount(ByVal intNumAs Integer) lblRecordCount.Text = intNumEnd Sub

|||URGGHGH Scratch that too. I was passing my variable as an integer. It has to be passed as a string. Working beautifully now. Thanks for the help! :)

Count Query

hi! I am new to this whole SQL language...
had a question about using a count function or if i shuld even be using a count function for this...
Data I have to work with I would like my result set to look like this:
ID ID2 ID ID1Count ID2Count ID3Count ID4Count
A 1 A 2 2 2 3
A 1 B 1 0 3 1
A 2
A 3
A 2
A 4
A 4
A 4
A 3
B 3
B 4
B 1
B 3
B 3
I am stumped.... ANy help would be great... thanks!
nick

select ID,
sum(case when ID2 = 1 then 1 else 0 end) as ID1Count,
sum(case when ID2 = 2 then 1 else 0 end) as ID2Count,
sum(case when ID2 = 3 then 1 else 0 end) as ID3Count,
sum(case when ID2 = 4 then 1 else 0 end) as ID4Count,
from table1
group by ID|||Thanks for the direction! got it to work like a charm!

count occurrence of character in a field using SQL

Is there a function that will enable me to count the number of instances a
particular character is in a feild? For instance...if I have a field named
number with a record with characters such as 00000111100000 and I what to do
a function that tells me the number of times the number 1 shows up in the
number field of that record. The result would be 4. Is this possible?SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
> Is there a function that will enable me to count the number of instances a
> particular character is in a feild? For instance...if I have a field
> named
> number with a record with characters such as 00000111100000 and I what to
> do
> a function that tells me the number of times the number 1 shows up in the
> number field of that record. The result would be 4. Is this possible?|||Perfect! Thank you!
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT LEN(column) - LEN(REPLACE(column, '1', '')) FROM table;
>
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:819C28E2-76E5-4392-BA01-A7BDDF2BA57A@.microsoft.com...
>
>

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

COUNT FUNCTION ON MULTIPLE COLUMNS

I have a database that contains a column for UnitName , BeginDate and
EndDate.

I want to pass two parameters (@.BeginDate and @.EndDate) and retrieve a
table of values

that include UnitName along with Counts for each UnitName.

SELECT UnitName, COUNT(BeginDate) AS Start
(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @.BeginDate AND
@.EndDate)
FROM Table
WHERE BeginDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY UnitName
ORDER BY UnitName

This works. But when I try to add another count by using a subselect I
get an error dealing with GROUP BY not including the column in my
subselect.

How is the best way to Count two columns using Group By.k4 wrote:

Quote:

Originally Posted by

I have a database that contains a column for UnitName , BeginDate and
EndDate.
>
I want to pass two parameters (@.BeginDate and @.EndDate) and retrieve a
table of values
>
that include UnitName along with Counts for each UnitName.
>
SELECT UnitName, COUNT(BeginDate) AS Start
(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @.BeginDate AND
@.EndDate)
FROM Table
WHERE BeginDate BETWEEN @.BeginDate AND @.EndDate
GROUP BY UnitName
ORDER BY UnitName
>
This works. But when I try to add another count by using a subselect I
get an error dealing with GROUP BY not including the column in my
subselect.
>
How is the best way to Count two columns using Group By.

Assuming the BeginDates are always <= the corresponding EndDates, you
can do:

SELECT UnitName,
COUNT(CASE WHEN BeginDate
BETWEEN @.BeginDate AND @.EndDate THEN 1 END) AS BeginDate,
COUNT(CASE WHEN EndDate
BETWEEN @.BeginDate AND @.EndDate THEN 1 END) AS EndDate
FROM tbl
WHERE BeginDate <= @.EndDate
AND EndDate >= @.BeginDate
GROUP BY UnitName
ORDER BY UnitName;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!
Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!
|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!
|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =
dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 ?
"Kalen Delaney" wrote:

> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
>
>
|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that[vbcol=seagreen]
> this
> query would have returned the value of 2 -- not 3 ?
>
> "Kalen Delaney" wrote:
give[vbcol=seagreen]
least[vbcol=seagreen]
of[vbcol=seagreen]
My[vbcol=seagreen]
the[vbcol=seagreen]
|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
(Please reply only to the newsgroup)

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact = Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact => Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:
> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
> us more info, and maybe even some sample data and sample output, or at least
> an explanation of what you're doing with that value if you want the rest of
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > The following SQL statement returns 38 records:
> >
> > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Rather than retrieving the recordset, I want to simply get a count. My
> > syntax must be wrong (same syntax as above but I added COUNT) because the
> > following query simply returns 38 records with the value "1":
> >
> > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > FROM Base_Contacts LEFT OUTER JOIN
> > Base_ContactAttributes ON Base_Contacts.ID_Contact => > Base_ContactAttributes.ID_Contact
> > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > GROUP BY Base_Contacts.ID_Contact
> > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> >
> > Any suggestions for this beginner? Thanks!
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
> > Hi
> >
> > GROUP BY always returns 1 row for each different value of the GROUP BY
> > column, so you're still getting one row for each different
> > Base_Contacts.ID_Contact
> > value. If you want the count of all values, don't use GROUP BY.
> >
> > I'm not exactly sure what you want to do with
> > Base_ContactAttributes.ID_Level_Value in the new query. You'll need to
give
> > us more info, and maybe even some sample data and sample output, or at
least
> > an explanation of what you're doing with that value if you want the rest
of
> > the solution.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> > news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> > > The following SQL statement returns 38 records:
> > >
> > > SELECT Base_Contacts.ID_Contact AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Rather than retrieving the recordset, I want to simply get a count.
My
> > > syntax must be wrong (same syntax as above but I added COUNT) because
the
> > > following query simply returns 38 records with the value "1":
> > >
> > > SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> > > FROM Base_Contacts LEFT OUTER JOIN
> > > Base_ContactAttributes ON Base_Contacts.ID_Contact => > > Base_ContactAttributes.ID_Contact
> > > WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> > > GROUP BY Base_Contacts.ID_Contact
> > > HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> > >
> > > Any suggestions for this beginner? Thanks!
> >
> >
> >|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person => dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
--
(Please reply only to the newsgroup)

COUNT function not working with GROUP BY

The following SQL statement returns 38 records:
SELECT Base_Contacts.ID_Contact AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Rather than retrieving the recordset, I want to simply get a count. My
syntax must be wrong (same syntax as above but I added COUNT) because the
following query simply returns 38 records with the value "1":
SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
FROM Base_Contacts LEFT OUTER JOIN
Base_ContactAttributes ON Base_Contacts.ID_Contact =
Base_ContactAttributes.ID_Contact
WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
GROUP BY Base_Contacts.ID_Contact
HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
Any suggestions for this beginner? Thanks!Hi
GROUP BY always returns 1 row for each different value of the GROUP BY
column, so you're still getting one row for each different
Base_Contacts.ID_Contact
value. If you want the count of all values, don't use GROUP BY.
I'm not exactly sure what you want to do with
Base_ContactAttributes.ID_Level_Value in the new query. You'll need to give
us more info, and maybe even some sample data and sample output, or at least
an explanation of what you're doing with that value if you want the rest of
the solution.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Remove your GROUP BY clause. You're counting the number in every group of
ID_Contacts.
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
> The following SQL statement returns 38 records:
> SELECT Base_Contacts.ID_Contact AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Rather than retrieving the recordset, I want to simply get a count. My
> syntax must be wrong (same syntax as above but I added COUNT) because the
> following query simply returns 38 records with the value "1":
> SELECT COUNT(DISTINCT Base_Contacts.ID_Contact) AS CONTACTS
> FROM Base_Contacts LEFT OUTER JOIN
> Base_ContactAttributes ON Base_Contacts.ID_Contact =
> Base_ContactAttributes.ID_Contact
> WHERE (Base_ContactAttributes.ID_Level_Value IN (10150, 10153))
> GROUP BY Base_Contacts.ID_Contact
> HAVING (COUNT(DISTINCT Base_ContactAttributes.ID_Level_Value) = 2)
> Any suggestions for this beginner? Thanks!|||Here's some sample data:
CREATE TABLE mypeople
(
id_person int IDENTITY (1, 1) NOT NULL,
name varchar(10) NULL
)
CREATE TABLE myattributes
(
id_person int NOT NULL,
id_attribute int NOT NULL
) ON [PRIMARY]
INSERT INTO mypeople VALUES('John')
INSERT INTO mypeople VALUES('Bill')
INSERT INTO mypeople VALUES('Jane')
INSERT INTO myattributes VALUES(1,1000)
INSERT INTO myattributes VALUES(1,1002)
INSERT INTO myattributes VALUES(1,1004)
INSERT INTO myattributes VALUES(1,1006)
INSERT INTO myattributes VALUES(1,1008)
INSERT INTO myattributes VALUES(2,1002)
INSERT INTO myattributes VALUES(2,1004)
INSERT INTO myattributes VALUES(3,1004)
Now my count distinct query is as follows:
SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
FROM dbo.mypeople INNER JOIN
dbo.myattributes ON dbo.mypeople.id_person =
dbo.myattributes.id_person
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
Notice that the count returned from this query is 3. Unless I'm not
understanding the query logic correctly, I would have thought that only two
people met the IN/HAVING clause criteria. In this example, only John and
Bill have attribute values of both 1002 and 1004. I would have thought that
this
query would have returned the value of 2 -- not 3 '
"Kalen Delaney" wrote:

> Hi
> GROUP BY always returns 1 row for each different value of the GROUP BY
> column, so you're still getting one row for each different
> Base_Contacts.ID_Contact
> value. If you want the count of all values, don't use GROUP BY.
> I'm not exactly sure what you want to do with
> Base_ContactAttributes.ID_Level_Value in the new query. You'll need to giv
e
> us more info, and maybe even some sample data and sample output, or at lea
st
> an explanation of what you're doing with that value if you want the rest o
f
> the solution.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "gstoa" <gstoa@.discussions.microsoft.com> wrote in message
> news:FC26E396-3FFF-408E-B326-F5DA12FACE3B@.microsoft.com...
>
>|||Thanks for the nice script. In your case, the count in the SELECT list is
counting the number of people who have any rows returned with values of
either 1002 or 1004 and so that is all the people, 3 of them.
I'm not sure exactly what the HAVING is doing here, but I do not think it
means what you think it means. I'll have to think about it to figure out
what it means here, or I'll just ask Itzik. :-)
In the meantime, you can rewrite this. You really don't need the mypeople
table at all, since you don't need any info that is just in that table. This
query gives you the list of id_person values that have exactly the two
attributes you need:
SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2
So, we can just make that a derived table, and count the rows in it:
SELECT count(*) FROM
(SELECT dbo.myattributes.id_person, COUNT(*) AS Expr1
FROM dbo.myattributes
WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
GROUP BY dbo.myattributes.id_person
HAVING COUNT(*) = 2) AS counts
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"gstoa" <gstoa@.discussions.microsoft.com> wrote in message
news:DB1E33DD-5BE0-46A4-8511-668EC549DC7D@.microsoft.com...
> Here's some sample data:
> CREATE TABLE mypeople
> (
> id_person int IDENTITY (1, 1) NOT NULL,
> name varchar(10) NULL
> )
> CREATE TABLE myattributes
> (
> id_person int NOT NULL,
> id_attribute int NOT NULL
> ) ON [PRIMARY]
> INSERT INTO mypeople VALUES('John')
> INSERT INTO mypeople VALUES('Bill')
> INSERT INTO mypeople VALUES('Jane')
> INSERT INTO myattributes VALUES(1,1000)
> INSERT INTO myattributes VALUES(1,1002)
> INSERT INTO myattributes VALUES(1,1004)
> INSERT INTO myattributes VALUES(1,1006)
> INSERT INTO myattributes VALUES(1,1008)
> INSERT INTO myattributes VALUES(2,1002)
> INSERT INTO myattributes VALUES(2,1004)
> INSERT INTO myattributes VALUES(3,1004)
> Now my count distinct query is as follows:
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
> Notice that the count returned from this query is 3. Unless I'm not
> understanding the query logic correctly, I would have thought that only
two
> people met the IN/HAVING clause criteria. In this example, only John and
> Bill have attribute values of both 1002 and 1004. I would have thought
that[vbcol=seagreen]
> this
> query would have returned the value of 2 -- not 3 '
>
> "Kalen Delaney" wrote:
>
give[vbcol=seagreen]
least[vbcol=seagreen]
of[vbcol=seagreen]
My[vbcol=seagreen]
the[vbcol=seagreen]|||gstoa,
you are looking for relational division. You may want to look up this
topic on the internet. (or you can just use Kalen's suggested query).
What your current query does is first calculate how many people have
attribute 1002 *or* attribute 1004. At the same time it will calculate
the number of different attributes that could be found and matched
either 1002 or 1004.
The first calculation results in 3, the second in 2. Up to this point,
the resultset is one row with these two values.
Then, all rows that do not have an attribute-count of 2 are removed. In
this case no rows are removed.
Finally, the requested columns of the resultset are returned. This is
the first calculated value of 3.
Gert-Jan
gstoa wrote:
> Here's some sample data:
<snip>
> SELECT COUNT(DISTINCT dbo.mypeople.name) AS Expr1
> FROM dbo.mypeople INNER JOIN
> dbo.myattributes ON dbo.mypeople.id_person =
> dbo.myattributes.id_person
> WHERE (dbo.myattributes.id_attribute IN (1002, 1004))
> HAVING (COUNT(DISTINCT dbo.myattributes.id_attribute) = 2)
<snip>
(Please reply only to the newsgroup)

COunt Function in Report

I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far.

I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial.

So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field

I also want to print the the same fields in the report total area.

Any ideas ?

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID")

=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")

Guys, I figured this one out.

Here is the solution :
=Sum(IIF Fields!InvStatus.Value = "PAID",1,0)

sql

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
>