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 Records Help

Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));Although I dont have the exact answer are you trying to do the following

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 2
B 12000 9 6
B 8000 2 2
B 16000 3 7

select conf, count(*) / (select count(*) from table)
from table
where self_score > opp_score
group by conf;

This query doesn't work because you need to group the divided query into the same group as the outer query. I would also like to know how this is done.|||Greetings,

I now have the answer :),

conf = conference

select s1.conf, avg_att As "Avg. Att", (wins / total) * 100 As "% Won"
from
(select conf, count(*) As wins
from help
where self_score > opp_score
group by conf) s1,
(select conf, avg(att) As avg_att, count(*) As total
from help
group by conf) s2
where s1.conf = s2.conf;

The average attendance is for the entire conference group regardless of whether or not they won. If you only want average attendance for the games they played then select the avg(att) in the top from-query as opposed to the second.

The sample table I used for this is shown below,

SQL> select * from help;

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 0
B 12000 4 6
B 8000 2 0
B 16000 3 7

And the results generated from the answer query

CONF Avg. Att % Won
---- ---- ----
A 7066.66667 66.6666667
B 12000 33.3333333

Cheers.|||- Thanks for your reply, It appears I left some info out, Ive been trying to adapt what you replied with but still no dice.

I am including the tables and data below. There are some null values as I am only inputing sample data that is needed in the reports.

CREATE TABLE SCHOOL
(school VARCHAR2(30),
conference VARCHAR2(25),
stadium_size NUMBER(6),
ticket_price NUMBER(4,2),
in_state_players NUMBER(2),
outstate_players NUMBER(2),
scholarships NUMBER(2),
graduate NUMBER(20));

INSERT INTO school VALUES
('Indiana Univ.', 'Big Ten', 53000, null, null, null, null, null);
INSERT INTO school VALUES
('Ohio State Univ.', 'Big Ten', 104000, null, null, null, null, null);
INSERT INTO school VALUES
('Penn State Univ.', 'Independent', 80000, null, null, null, null, null);
INSERT INTO school VALUES
('Univ. of Pittsburgh', 'Independent', 51000, null, null, null, null, null);
INSERT INTO school VALUES
('Pondunk Univ.', 'Independent', 44000, null, null, null, null, 35);
INSERT INTO school VALUES
('Violator Univ.', 'Independent', 39000, null, null, null, null, 47);

CREATE TABLE SCHOOL_INCIDENTS
(school VARCHAR2(30),
idate DATE,
incident_code NUMBER(5));

INSERT INTO school_incidents VALUES
('Indiana Univ.',null, 17983);
INSERT INTO school_incidents VALUES
('Ohio State Univ',null, 12891);
INSERT INTO school_incidents VALUES
('Penn State Univ.',null, 17250);

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 41000, 0, 7, null, null);

Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Big Ten 46000 17983
Big Ten 45000 17983
Big Ten 44000 17983
Big Ten 43000 17983
Big Ten 42000 17983
Big Ten 41000 17983
Big Ten 40000 17983
Big Ten 39000 17983
Big Ten 38000 17983
Big Ten 37000 17983
Big Ten 36000 17983

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Independent 51000 17250
Independent 50000 17250
Independent 49000 17250
Independent 48000 17250
Independent 47000 17250
Independent 46000 17250
Independent 45000 17250
Independent 44000 17250
Independent 43000 17250
Independent 42000 17250
Independent 41000 17250

Count Query Performance

I have a query that seems simple but apparently is not.Any thoughts on performance optimization would be helpful.The following query works, but it takes 20 minutes to run.

WITH

MEMBER [Measures].[90-100] AS

'COUNT( FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.90)

)'

MEMBER [Measures].[80-90] AS

'COUNT(FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)'

SELECT

{[Measures].[90-100], [Measures].[80-90]} ON COLUMNS,

NON EMPTY EXISTING [School].[By District Area].[School].MEMBERS ON ROWS

FROM [Student Engagement]

There has got to be a better way. My guess is that somehow I need to limit the resulting set of schools * students that get counted to only the nonempties, but I’m not sure how to formulate the MDX.

Any ideas?

Chris

Instead of EXISTING in the calculated measures, which isn't needed unless other attributes of the [Student] dimension are placed on query axes, you could try NonEmpty(), like:

WITH

MEMBER [Measures].[90-100] AS

COUNT( FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.90)

)

MEMBER [Measures].[80-90] AS

COUNT(FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)

where [AttendFactCount] is some cube measure from the relevant measure group (assuming that [Avg Attendance] is a calculated measure like: [PresentSum] / [AttendFactCount]).|||

Deepak-

Thank you! Inserting the “NonEmpty” worked. The query returned in 5 seconds.

The question is why.Given, that I had “Non Empty” in the select statement and the NonEmptyBehavior(NEB) set to [Measures].[Absence] for the calculated member [Measures].[Avg Attendance] (Avg Attendance = Absence/ Enrollment) in the initial query,I would have assumed that the query engine would suppress the non empties automatically based on the NEB.Can you help me understand?

Chris

|||

Chris,

The “Non Empty” in the select statement wouldn't help because, if the 2 calculated measures are simply defined as counts, they could be 0, but never empty. Regarding the NEB, I'm not sure; but it could be that the query optimizer doesn't infer from the condition: [Avg Attendance] > 0.9 that [Avg Attendance] isn't empty. You could check whether changing the condition to: Not IsEmpty([Avg Attendance]) And [Avg Attendance] > 0.9 speeds up the query.

Count of Counts in SQL Server

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parulthat doesn't make any sense

a count of a count will always be 1|||why is that so? how do we get a count of all those rows that appear in the inner query?|||okay, let's break this problem down into steps

could you please run the inner query all by itself and show me what you get|||Since the inner select query has a group by field, the number of rows it returned may be more than one also.|||it shows you different counts by student first name; the result looks like:
count
--
5
24
2
23
2|||ah yes, okay, then i think what you want is this --select count(*)
from (
select count(*)
from student
group by firstName
having count(*) > 1
) as counts|||Exactly, but this is giving me the following error:

No column was specified for column 1 of 'counts'.|||I got it! The inner select column needs an alias.
select count(*)
from (
select count(*) as 'cnt'
from student
group by firstName
having count(*) > 1
) as counts

Thanks so much for your help!

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

I am using Oracle. Can anyone show me how to count distinctly for each Genre ID from following DATA?
GENRE_ID GENRE_DESCRIPTION MOVIE_ID
--- ------- -------
AC Action 1
DR Drama 1
TR Thriller 1
CO Comedy 2
RO Romance 2
CO Comedy 3
CR Crime 3
TR Thriller 3
AN Animation 4
CO Comedy 4
FA Family 4
AC Action 5
FY Fantasy 5
TR Thriller 5
AC Action 6
AD Adventure 6
FY Fantasy 6
HO Horror 7
MY Mystery 7
TR Thriller 7
AD Adventure 8
AN Animation 8
CO Comedy 8
CR Crime 9
DR Drama 9
HO Horror 100
MY Mystery 100
TR Thriller 100

What i want is something like this where it counts the numbers of Movie_id for each genre where there is no duplicate date for genre_id or movie_id.
Genre_ID Genre_Description Number of Movies
AC Action 2Hi,

Try this:
Select Genre_ID,Genre_Description,sum(movie_id) from table_name group by Genre_ID,Genre_Description;|||Please, move this tread to Oracle forum.

Count Distinct

Hello,

I was wondering does count(distinct) work on multiiple columns? I get syntax error on "," with the following code.

select count(distinct cookie1, cookie2) from BookersLookers_DataSet

Thanks,

-Lawrence

You cant pass more than one column in COUNT function..

Use the following query...

Code Snippet

Select Count(*) From

(Select cookie1,cookie2 From BookersLookers_DataSet Group By cookie1,cookie2) as Data

--Or

Select Count(*) From

(Select Distinct cookie1,cookie2 From BookersLookers_DataSet) as Data

|||

Actually, to get the count(), you need to use a GROUP BY, and if you use a GROUP BY, then DISTINCT is not necessary (redundent).

Code Snippet

SELECT
Cookie1,
Cookie2,
[Count] = count(1)
FROM BookersLookers_Dataset
GROUP BY
Cookie1,
Cookie2

count + excludeempty gives errors

Hi,

I try to make a calculated cell in SSAS 2000, in the standard foodmart 2000 example.
I try a technique from the following page:
http://msdn2.microsoft.com/en-us/library/aa902637(SQL.80).aspx

For instance, I want to know how many married people there are in a salarygroup in the cube 'Sales'.
I define the following MDX instruction for the calculated member "Married customers":
Count(CrossJoin( {[Marital Status].[M]},
Descendants([Customers].CurrentMember, [Customers].[(All)])), excludeempty)
I get nothing but errors when browsing the cube. Every cell states #ERR
When I do not define 'excludeempty' everything's fine.
How come?

Thanks in advance
I have to guess here, because you don't specify the entire statement, but I am pretty sure that the #ERR that you are getting is "Infinite recursion" one. Which dimension did you define the "Married customers" calculated member ? If in measures - you will get this error. So either you move the calculated member to the Customers dimension, or you explicitly qualify your set with one of the cube measures for which you want to do math.|||Yes, I have defined it in measures.

As I, for instance, execute the following query in the MDX Sample Application (in the foodmart2000 example) I get these errors too:

WITH MEMBER [Measures].[allcustom] AS 'COUNT([Customers].[Name].MEMBERS, excludeEmpty)'
SELECT
[Gender].MEMBERS ON COLUMNS,
[Yearly Income].MEMBERS ON ROWS
FROM Sales
WHERE [Measures].[allcustom]

If I, for instance, make two calculated cells where I calculate the amount of male and female customers, like as follows:
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},

{ [Gender].[Gender].&[M] }
))
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},

{ [Gender].[Gender].&[F] }
))
And I execute the following query in the MDX sample application:
SELECT
[Yearly Income].MEMBERS ON COLUMNS,
{ [Measures].[Male customers], [Measures].[female customers]} ON ROWS
FROM Sales

With this, I get a similar result as what I wanted in first query in this post.
But I do not want to make calculated cells for every member of a dimension.
But how do I make one calculated cell for that?

Thanks,

Eyso
|||Thanks mister Pasumansky! It works great. :)
I didn't knew that if I changed the parent dimension of a calculated member, I could still get the counts as results rather than as metadata.

count + excludeempty gives errors

Hi,

I try to make a calculated cell in SSAS 2000, in the standard foodmart 2000 example.
I try a technique from the following page:
http://msdn2.microsoft.com/en-us/library/aa902637(SQL.80).aspx

For instance, I want to know how many married people there are in a salarygroup in the cube 'Sales'.
I define the following MDX instruction for the calculated member "Married customers":
Count(CrossJoin( {[Marital Status].[M]},
Descendants([Customers].CurrentMember, [Customers].[(All)])), excludeempty)
I get nothing but errors when browsing the cube. Every cell states #ERR
When I do not define 'excludeempty' everything's fine.
How come?

Thanks in advanceI have to guess here, because you don't specify the entire statement, but I am pretty sure that the #ERR that you are getting is "Infinite recursion" one. Which dimension did you define the "Married customers" calculated member ? If in measures - you will get this error. So either you move the calculated member to the Customers dimension, or you explicitly qualify your set with one of the cube measures for which you want to do math.|||Yes, I have defined it in measures.

As I, for instance, execute the following query in the MDX Sample Application (in the foodmart2000 example) I get these errors too:

WITH MEMBER [Measures].[allcustom] AS 'COUNT([Customers].[Name].MEMBERS, excludeEmpty)'
SELECT
[Gender].MEMBERS ON COLUMNS,
[Yearly Income].MEMBERS ON ROWS
FROM Sales
WHERE [Measures].[allcustom]

If I, for instance, make two calculated cells where I calculate the amount of male and female customers, like as follows:
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},

{ [Gender].[Gender].&[M] }
))
COUNT(NonEmptyCrossjoin(
{[Customers].[Name].MEMBERS},

{ [Gender].[Gender].&[F] }
))
And I execute the following query in the MDX sample application:
SELECT
[Yearly Income].MEMBERS ON COLUMNS,
{ [Measures].[Male customers], [Measures].[female customers]} ON ROWS
FROM Sales

With this, I get a similar result as what I wanted in first query in this post.
But I do not want to make calculated cells for every member of a dimension.
But how do I make one calculated cell for that?

Thanks,

Eyso|||Thanks mister Pasumansky! It works great. :)
I didn't knew that if I changed the parent dimension of a calculated member, I could still get the counts as results rather than as metadata.

Count ( Distinct Case ..) syntax error

I am getting a syntax error for the following piece of code:

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)

I don't understand what's wrong with it. I came across constructs like

Select Count(Distinct Case When ...Then ID End ) ...

and also

Select Distinct Count(Case When ...)...

What I want is the first one, a count of the distinct IDs.

What am I missing?

Magic:

I am not sure what you are getting; could you post your error message? When I run what follows it compiles OK and seems to run OK:

Code Snippet

select Count(Distinct Case When (StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)
and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52)
Then ProjectID end
) as distinctCount
from ( select 1 as startDate, 1 as ProjectStatusId, 1 as projectId) x

/*
distinctCount
-
0

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
*/

|||My code is below. I can't use the insert code feature because whenever I click on that it makes my whole post dissappear. I'm using Firefox.

The error is:
Incorrect syntax near 'distinct'.

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End) Over (Partition By DepartmentDetails.DepartmentName, ProjectCategory) [NewRequests]

From #tempResourceAllocation
Inner join dbo.DepartmentDetails
On (#tempResourceAllocation.ParentDepartmentID = DepartmentDetails.DepartmentID)

Order By ProjectCategory

|||

The problem seems to be when using "distinct" inside an aggregate function and the "over" clause. I haven't be able to find anything related to this in BOL.

AMB

|||

I fully agree with AMB; however, I was able to get my version to work using GROUP BY instead of OVER. Give GROUP BY a try instead. What I have looks like this:

Code Snippet

Select DepartmentDetails.DepartmentName
,ProjectCategory
,Count(distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)
as [NewRequests]
from ( select 1 as startDate,
1 as projectStatusId,
1 as departmentName,
1 as projectCategory,
1 as projectId
) as departmentDetails
group by DepartmentDetails.DepartmentName, ProjectCategory
order by ProjectCategory

/*
DepartmentName ProjectCategory NewRequests
-- --
1 1 0
*/

|||

I also see a potential problem with your date range. It looks to me like you have the same TO and FROM date if you are trying to get data from the previous month, change

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))

to

Code Snippet

and StartDate < dateadd(month, datediff(month, 0, getdate()), 0))

|||Yes, thanks, I was playing around getting some values from the previous one month intervals and when I switched it back I missed that.

Thanks for the input everyone.

I don't really know what to do since I was using Partition Over as another way to create subtotals based on the category but only for a subset of the columns in the table. I tried with Rollup but I couldn't get this functionality because it forces me to put all the columns in Group By and it messes up my layout giving me summary totals based on different criteria rather than solely on the Category field.

Even so Rollup doesn't work with Distinct aggregates which is a problem because I do have several entries with the same key of interest in my table just because in someother column I have distinct values for the same key and counting will include duplicates also.

|||

hi, did you try this?

Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID else 0 End)

|||Whether it counts a 1 or a 0 isn't the result still one? As in one item that got counted?
|||

I think Tolga has a good point NULL does not help you towards a distinct count. Notice for this two-record select that that one of the entries is null. Also, note that the count is "1" and not "2":

Code Snippet

select count(distinct what) as theCount from (
select 1 as what union select null
) a

/*
theCount
--
1
*/

|||

yes you are right, result still one,

okey, try to sum,

sum(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then ProjectID End)Count(Distinct Case When
(StartDate >= dateadd(month, datediff(month, 0, getdate())-1, 0) and StartDate < dateadd(month, datediff(month, 0, getdate())-1, 0))
And ProjectStatusID In (49, 50, 51, 52) Then 1 else 0 End)

Couldn't connect server

Hi to all,
When I try to connect SQL 2000 Enterprise client with SQL
Server 2000 Enterprise Server , the following error
message displayed.
Unable to connect to server
Server:Msg 17,Level 16,State 1
Microsoft ODBC SQL Server Driver TCP/IP Sockets SQL
Server does not exist or access denied
How can i solve this problem?
Database Server configuration:
Windows Server 2003 with SQL Server 2000 Enterprise
Edition
Advance thankx
Sankar.First, verify you can make a network connection like a mapped drive to the
server.
Second, open Client Network Utility on your workstation, Server Network
Utility on the server (both programs are in the SQL Program group) and
verify you are both listening on the same protocols in the same order.
Lastly, you should verify MSSQLServer service is running on the server and
that there are no firewalls in between your computers which might be
blocking ports.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Sankar" <anonymous@.discussions.microsoft.com> wrote in message
news:04c801c3d8ea$36ab78e0$a301280a@.phx.gbl...

quote:

---

> Hi to all,
> When I try to connect SQL 2000 Enterprise client with SQL
> Server 2000 Enterprise Server , the following error
> message displayed.
>
> Unable to connect to server
> Server:Msg 17,Level 16,State 1
> Microsoft ODBC SQL Server Driver TCP/IP Sockets SQL
> Server does not exist or access denied
> How can i solve this problem?
> Database Server configuration:
> Windows Server 2003 with SQL Server 2000 Enterprise
> Edition
>
> Advance thankx
> Sankar.

---

|||Also, since you are attempting a TCP?IP connection verify that you can ping
the SQL Server machine by name. If not, try specifying the SQL Server
machine name ip address as the server name when attempting to connect. If
the first fails but the second succeeds then there is a name resplution
problem. If the second fails as well then there could be a network related
problem that is preventing the client from accessing the SQL Server.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||> the SQL Server machine by name. If not, try specifying the SQL Server

quote:

---

> machine name ip address as the server name when attempting to connect. If
> the first fails but the second succeeds then there is a name resplution
> problem. If the second fails as well then there could be a network related
> problem that is preventing the client from accessing the SQL Server.

---

I have the same problem but I can ping the machine.
Mike Collier BSc( Hons) Comp Sci
Get a copy of AdoAnywhere Browser FREE if you register for the forum
quickly...
http://www.adoanywhere.com/forum
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:h0mDbBU2DHA.2184@.cpmsftngxa07.phx.gbl...

quote:

---

> Also, since you are attempting a TCP?IP connection verify that you can

---

ping

quote:

---

> the SQL Server machine by name. If not, try specifying the SQL Server
> machine name ip address as the server name when attempting to connect. If
> the first fails but the second succeeds then there is a name resplution
> problem. If the second fails as well then there could be a network related
> problem that is preventing the client from accessing the SQL Server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

|||Work through this article:
328306 INF: Potential Causes of the ("SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Could we discuss Web Data Administrator one last time?

I installed MSDE (SQL2KDeskSP3) from the command prompt with the following command line:

Setup.exe /qb+ INSTANCENAME=newsdb_engine DISABLENETWORKPROTOCOLS=1 SAPWD=dotNet

I then installed WebDataAdmin using all of the defaults.

I opened WebDataAdmin and at the login entered these credentials:

Username: sa
Password: dotNet
Server: newsdb_engine

Each time the error is returned that the username or password is invalid and/or the server does not exist.

So I tried a different server name, adding the computer name:
Server: AZT12YXM/newsdb_engine

Same error message occurred.

Then I read that SQL Authentication mode has to have the correct registry setting.
I went to HKLM/Microsoft/MSSqlServer/MSSqlServer/ and changed the login from "1" to "0"
and rebooted.

Same error message occurred.

I do have a developer edition of SqlServer 2000, but I want to be able to install msde and configure it without enterprise tools so that msde is the sole database engine (for deployment reasons).

Could someone suggest a list of steps from start to finish for installing msde on a machine where the instance you create is the sole database on the target machine and how to configure it to be Web Data Administrator "friendly"?

Maybe this could be the "one-stop" Web Data Admin keyword search thread.

Thank you.The servername should be: MachineName\InstanceName

Cheers
Ken

Could someone please explain the following...

As previous threads have indicated I've been trying to figure out what's slowing down our 3rd party ETL tool.

My research has figured out the culprit, but now I need some advice on the best way to handle it.

Here's what's going on...
SPID 1 = dynamic sql passed from Crystal
SPID 2 = ETL tool
SPID 3 = report in stored proc

Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.

Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off

Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1

Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.

My questions:
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?

Thanks in advanceSince SPID 1 has a very inefficient query, it is probably doing a table scan. This will incur a shared lock on the entire table. With a shared lock on the table, SPID2 must wait to do any insert/update/delete. SPID 2 can read all day long, but any write activity must wait until his intent lock is upgraded to an exclusive lock. SPID 3 is waiting, because it will need to get its intent shared lock upgraded to a shared lock, and the intent exclusive lock (from SPID2) is preventing that upgrade.

Now, if SPID 3 wanted to cash in enough frequent flier miles, they could bring a companion along, but only in Economy class, unless they brought no luggage, in which case they could travel Business class. Alternatively, they could upgrade to first class, but only on Yak Shaving day.

Does that help?|||Yes. Given that SPID 1's are out of our control. How would you try to rectify? I don't think you can use WITH NOLOCK through older Crystal? But that would be perfect solution right?|||Well wait a minute.
I've seen where SPID 1 blocks SPID 3 as well. How could that be? (SPID 2 is not running at all) and both are just selects. Spid 3 has been indexed and optimized while 1 has not.

I'm using sp_blocker_pss80|||How can 2 selects block each other?|||What is the waittype and waitresource on SPID3, then? It is possible, that they could be waiting on an OAM page in tempdb (resource 2:1:2). In which case, there is a hot fix out for it.|||OK, when we say spid 1, it's just an example right, I mean it's gotta be 50+...

In any case...

KILL 1

That should solve the problem|||Ok Here's what I've got. I did a search on the 2:1:2 and didn't find anything in the output file.
Here's a small excerpt from the proc...
There's actually about 15 more of the Spid 70s.

What causes multiple instances of Spid 70 to show up? At this point in time it looks like nothing is going on. Just a bunch of waiting.
63 sleeping 70 1 TAB: 7:1177771253 []
70 sleeping 0 0 7:1:2233567
70 sleeping 0 0 7:1:1699288
70 sleeping 0 0 7:1:3235145
70 sleeping 0 0 7:1:2014717

DBCC SQLPERF(WAITSTATS)
Wait Type Requests Wait Time Signal Wait Time
---------- ----- ----- ------
MISCELLANEOUS 15791.0 0.0 0.0
LCK_M_SCH_S 0.0 0.0 0.0
LCK_M_SCH_M 6.0 2075406.0 0.0
LCK_M_S 24.0 72611.0 65095.0
LCK_M_U 1.0 60031.0 0.0
LCK_M_X 1163.0 445734.0 330.0
LCK_M_IS 24.0 9562923.0 0.0
LCK_M_IU 0.0 0.0 0.0
LCK_M_IX 3.0 31750.0 0.0
LCK_M_SIU 0.0 0.0 0.0
LCK_M_SIX 0.0 0.0 0.0
LCK_M_UIX 0.0 0.0 0.0
LCK_M_BU 0.0 0.0 0.0
LCK_M_RS_S 0.0 0.0 0.0
LCK_M_RS_U 0.0 0.0 0.0
LCK_M_RIn_NL 0.0 0.0 0.0
LCK_M_RIn_S 0.0 0.0 0.0
LCK_M_RIn_U 0.0 0.0 0.0
LCK_M_RIn_X 0.0 0.0 0.0
LCK_M_RX_S 0.0 0.0 0.0
LCK_M_RX_U 0.0 0.0 0.0
LCK_M_RX_X 0.0 0.0 0.0
SLEEP 2.3878072E+7 4.067754E+9 4.0649859E+9
IO_COMPLETION 2665064.0 2.8815246E+7 87694.0
ASYNC_IO_COMPLETION 38.0 2488516.0 0.0
RESOURCE_SEMAPHORE 52.0 747943.0 250123.0
DTC 0.0 0.0 0.0
OLEDB 1546014.0 1.5816713E+9 4.9219424E+7
FAILPOINT 0.0 0.0 0.0
RESOURCE_QUEUE 8648136.0 3.5166684E+9 4.0521631E+9
ASYNC_DISKPOOL_LOCK 471.0 0.0 0.0
UMS_THREAD 0.0 0.0 0.0
PIPELINE_INDEX_STAT 2.0 0.0 0.0
PIPELINE_LOG 0.0 0.0 0.0
PIPELINE_VLM 0.0 0.0 0.0
WRITELOG 709526.0 1.2246787E+7 26791.0
PSS_CHILD 0.0 0.0 0.0
EXCHANGE 7204.0 165437.0 1329.0
XCB 0.0 0.0 0.0
DBTABLE 0.0 0.0 0.0
EC 0.0 0.0 0.0
TEMPOBJ 0.0 0.0 0.0
XACTLOCKINFO 0.0 0.0 0.0
LOGMGR 0.0 0.0 0.0
CMEMTHREAD 754511.0 17897.0 11913.0
CXPACKET 4.5454468E+7 1.99537E+9 1.0079291E+7
PAGESUPP 59543.0 166747.0 1616.0
SHUTDOWN 0.0 0.0 0.0
WAITFOR 1821.0 5.4635088E+7 5.4635088E+7
CURSOR 0.0 0.0 0.0
EXECSYNC 20.0 0.0 0.0
LATCH_NL 0.0 0.0 0.0
LATCH_KP 0.0 0.0 0.0
LATCH_SH 1808.0 424271.0 46.0
LATCH_UP 26838.0 4159827.0 595.0
LATCH_EX 2.3463176E+7 1.0278586E+8 984947.0
LATCH_DT 0.0 0.0 0.0
PAGELATCH_NL 0.0 0.0 0.0
PAGELATCH_KP 340.0 0.0 0.0
PAGELATCH_SH 5.644284E+7 664660.0 274523.0
PAGELATCH_UP 2996847.0 2414701.0 66588.0
PAGELATCH_EX 4.1701784E+7 438060.0 174322.0
PAGELATCH_DT 0.0 0.0 0.0
PAGEIOLATCH_NL 0.0 0.0 0.0
PAGEIOLATCH_KP 0.0 0.0 0.0
PAGEIOLATCH_SH 4.9351192E+7 4.4433366E+8 792880.0
PAGEIOLATCH_UP 24098.0 3505965.0 632.0
PAGEIOLATCH_EX 6859974.0 3.7601116E+7 40328.0
PAGEIOLATCH_DT 0.0 0.0 0.0
TRAN_MARK_NL 0.0 0.0 0.0
TRAN_MARK_KP 0.0 0.0 0.0
TRAN_MARK_SH 0.0 0.0 0.0
TRAN_MARK_UP 0.0 0.0 0.0
TRAN_MARK_EX 0.0 0.0 0.0
TRAN_MARK_DT 0.0 0.0 0.0
NETWORKIO 6047450.0 1.2257828E+8 0.0
Total 2.706583E+8 1.1991903E+10 8.2338621E+9|||OK, when we say spid 1, it's just an example right, I mean it's gotta be 50+...

In any case...

KILL 1

That should solve the problem

Yes just an example. And I wish it could be that simple.|||to answer your question they are PAGEIOLATCH_SH wait types|||I think that only one person at a time can have a latch, so if one person is sitting on that latch, but stuck waiting for a mass update, then you could see this behaviour. I will have to go look that up in Kalen's book.

As for SPID 70, the most common cause is Parallelism. See if the waittype is CX_PACKET, or some such. The waittype should be 0x208.

Could not start SQL Server

SQL server is not starting, logs following error in
windows log
Error: 9003, Severity: 20,State: 1
The LSN (4:482:1) passed to log scan in database 'model'
is invalid
Is any one having solution for this?Manoj
It looks like the model database has become corrupted due to the power
failure.
Try to start the SQL Server as an application from a command prompt. For
more information, please refer to the topics: 'Starting SQL Server
Manually' and 'Using Startup Options' on Microsoft SQL Server Books Online.
Look if it does not help you so in this situation your best bet is to call
MS PSS
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Hi,
Seems your Model database is corrupt,
1. Stop SQL server
2. Copy the .MDF and .LDF files (Belongs to MODEL database) to a different
ent location
3. From a different SQL Serevr copy the Model databases .MDF and .LDF files
to the same folder. Overwrite the old files
4. Now start the SQL server.
Most probably this will work out.
Thanks
Hari
MCDBA
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Thanks Hari !! I will try this option
>--Original Message--
>Hi,
>Seems your Model database is corrupt,
>1. Stop SQL server
>2. Copy the .MDF and .LDF files (Belongs to MODEL
database) to a different
>ent location
>3. From a different SQL Serevr copy the Model
databases .MDF and .LDF files
>to the same folder. Overwrite the old files
>4. Now start the SQL server.
>
>Most probably this will work out.
>Thanks
>Hari
>MCDBA
>
>"Manoj" <manoj_raheja@.hotmail.com> wrote in message
>news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
>
>.
>|||Make sure that other SQL Server has the same collation. SQL Server does not
support different collations across the system databases.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:d07801c3eeee$ed440170$a401280a@.phx.gbl...
> Thanks Hari !! I will try this option
> database) to a different
> databases .MDF and .LDF files

Could not start SQL Server

SQL server is not starting, logs following error in
windows log
Error: 9003, Severity: 20,State: 1
The LSN (4:482:1) passed to log scan in database 'model'
is invalid
Is any one having solution for this?Manoj
It looks like the model database has become corrupted due to the power
failure.
Try to start the SQL Server as an application from a command prompt. For
more information, please refer to the topics: 'Starting SQL Server
Manually' and 'Using Startup Options' on Microsoft SQL Server Books Online.
Look if it does not help you so in this situation your best bet is to call
MS PSS
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Hi,
Seems your Model database is corrupt,
1. Stop SQL server
2. Copy the .MDF and .LDF files (Belongs to MODEL database) to a different
ent location
3. From a different SQL Serevr copy the Model databases .MDF and .LDF files
to the same folder. Overwrite the old files
4. Now start the SQL server.
Most probably this will work out.
Thanks
Hari
MCDBA
"Manoj" <manoj_raheja@.hotmail.com> wrote in message
news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> SQL server is not starting, logs following error in
> windows log
> Error: 9003, Severity: 20,State: 1
> The LSN (4:482:1) passed to log scan in database 'model'
> is invalid
> Is any one having solution for this?
>|||Thanks Hari !! I will try this option
>--Original Message--
>Hi,
>Seems your Model database is corrupt,
>1. Stop SQL server
>2. Copy the .MDF and .LDF files (Belongs to MODEL
database) to a different
>ent location
>3. From a different SQL Serevr copy the Model
databases .MDF and .LDF files
>to the same folder. Overwrite the old files
>4. Now start the SQL server.
>
>Most probably this will work out.
>Thanks
>Hari
>MCDBA
>
>"Manoj" <manoj_raheja@.hotmail.com> wrote in message
>news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
>> SQL server is not starting, logs following error in
>> windows log
>> Error: 9003, Severity: 20,State: 1
>> The LSN (4:482:1) passed to log scan in database 'model'
>> is invalid
>> Is any one having solution for this?
>
>.
>|||Make sure that other SQL Server has the same collation. SQL Server does not
support different collations across the system databases.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:d07801c3eeee$ed440170$a401280a@.phx.gbl...
> Thanks Hari !! I will try this option
> >--Original Message--
> >Hi,
> >
> >Seems your Model database is corrupt,
> >
> >1. Stop SQL server
> >2. Copy the .MDF and .LDF files (Belongs to MODEL
> database) to a different
> >ent location
> >3. From a different SQL Serevr copy the Model
> databases .MDF and .LDF files
> >to the same folder. Overwrite the old files
> >4. Now start the SQL server.
> >
> >
> >Most probably this will work out.
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Manoj" <manoj_raheja@.hotmail.com> wrote in message
> >news:d03f01c3eeea$1f354a90$a401280a@.phx.gbl...
> >> SQL server is not starting, logs following error in
> >> windows log
> >>
> >> Error: 9003, Severity: 20,State: 1
> >> The LSN (4:482:1) passed to log scan in database 'model'
> >> is invalid
> >>
> >> Is any one having solution for this?
> >>
> >
> >
> >.
> >|||Hi! Hari Prasad,
Heartly thanks for your comment. I was stuck with the same problem & after googling for quite sometime i came across your comment. I am all set with my DB now. Thanks again.
From http://www.developmentnow.com/g/118_2004_2_0_0_469115/Could-not-start-SQL-Server.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||What's the error? Could you post the error form the error log?
--
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Community)
SQLTips, SQL Scripts, SQL Forums, SQL Articles, SQL Radio, SQL Server 2008,
SQL Blogs, SQL Clinic and a lot of SQL Server fun.
"Girish Sharma (QA Engg.)" wrote:
> Hi! Hari Prasad,
> Heartly thanks for your comment. I was stuck with the same problem & after googling for quite sometime i came across your comment. I am all set with my DB now. Thanks again.
>
> From http://www.developmentnow.com/g/118_2004_2_0_0_469115/Could-not-start-SQL-Server.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

Could not start SQL Agent

Hello.
I have SQL Server MSDE running Windows 2000.
When I start SQL Agent gives me the following error:"Could not start
the SQLAgent$SPSQL service on Local Computer. The service did not
return an error..."
An
Can anyone help me?
Thanks.
ticha
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message930459.html
Hi
Look in the Windows Event Log and the Agent log file in the LOGs directory.
You may get more information there.
Regards
Mike
"ticha" wrote:

> Hello.
> I have SQL Server MSDE running Windows 2000.
> When I start SQL Agent gives me the following error:"Could not start
> the SQLAgent$SPSQL service on Local Computer. The service did not
> return an error..."
> An
> Can anyone help me?
> Thanks.
>
> --
> ticha
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message930459.html
>