February 9, 2011 at 4:21 am
Dear All,
i am new to sql server so that why i am asking a very basic question. i am running a query against a tree structure saved in db in linear fashion. i want to run the query to get the document counts bases on the specific field value following are both queriues
1.
SELECT COUNT(FileID) as SearchHits FROM tblFiles WHERE tblFiles.FolderID IN (55,677) AND Field124= 'M1_1' AND Field125= 'M2_1' GROUP BY FileID
RESULT IS 2 rows each row has value 1
2. SELECT COUNT(FileID) as SearchHits FROM tblFiles WHERE tblFiles.FolderID IN (55,677) AND Field124= 'M1_1' AND Field125= 'M2_1'
REsult is 1 row has value 2 (correct result which i want)
my question is what is the behavior of Group by clause in this secnarion.
hope so u all understand my question
February 9, 2011 at 8:03 am
From BOL: Groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions. One row is returned for each group
The first statement is saying give me a count of all FileIDs by FileID. So, if FileID is unique in the table then it will return 1 for EACH fileid found in the table. If you change the select to be SELECT FCFRFiles.FileID, COUNT(FCFRFiles.FileID) , then you will see each different FileID and the count of 1. The second is just giving you a count of all FileIDs in the table, so you will get one number that represents the count of the table.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply