surajkandukuri Posted November 10, 2009 Share Posted November 10, 2009 Hi all, I am experiencing a strange behaviour with my query SELECT COUNT( * ) FROM cat_completiondate WHERE `EmployeeNumber` =34550 which is returning '1' BUT my database has 2 rows for the EmployeeNumber=34550 My table cat_completiondate has a composite primary key with EmployeeNumber and Positionheld Can anyone help me please !! Quote Link to comment Share on other sites More sharing options...
Maq Posted November 10, 2009 Share Posted November 10, 2009 Weird. Try to do a regular SELECT * and see how many rows you get back because what you explained is impossible. Maybe you did a row count on COUNT(*) which would return 1, but the value would be 2. Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Hi Maq, I did a regular SELECT on EmployeeNumber=34550 it is returning also returning ONLY 1 Row when it should return 2 rows. I do not have any constraints also on cat_completiondate TABLE. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 That means you have only one row with this ID. What makes you think you have two? Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Hi Maq, I have two rows for the same employeenumber , my table contains EMPLOYEENUMBER with multiple job positions so my table looks something like this EmployeeNumber JobPosition CategoryNumber 34550 1 1 34550 1 2 34550 1 5 My intention was to count the number of rows for EmployeeNumber for the each job position in this table so My intention result is 3 if go for 34550 and 1 Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 And you say that SELECT * FROM cat_completiondate WHERE EmployeeNumber = 34550 returns one row only? Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Yes It is returning only 1 row !!! Even I cannot figure out why it is like that. In Phpmyadmin I can see 3 rows in that table and still returning only 1 row for the select statement. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 Can we see your code? Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Sure !! EmployeeNumber Cat_Num CategoryJobPosition 34450 2 1 34550 1 1 My code is $sql = "SELECT count(*) as count FROM `cat_completiondate` WHERE `EmployeeNumber`=34550 and `CategoryJobPosition`=1"; $result=mysql_query($sql); $row=mysql_fetch_array($result); echo($row['count']); OUTPUT is 1 Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 And how many rows have `CategoryJobPosition`=1 ? Ok I see now EmployeeNumber Cat_Num CategoryJobPosition 34450 2 1 34550 1 1 Well 34450 is not 34550 Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Sorry !! I didnot display it correctly EmployeeNumber Cat_Num CategoryJobPosition 34450 2 1 34550 1 1 This is my database !!! Quote Link to comment Share on other sites More sharing options...
Maq Posted November 10, 2009 Share Posted November 10, 2009 It's also weird that you have a table called "cat_completiondate" with no dates... Sorry !! I didnot display it correctly EmployeeNumber Cat_Num CategoryJobPosition 34450 2 1 34550 1 1 This is my database !!! They're still different. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 And you have 34450 in first row and 34550 in second. Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 Yes, I do have 2 rows seperately. Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 Yes, but only one has `EmployeeNumber`=34550 Quote Link to comment Share on other sites More sharing options...
Maq Posted November 10, 2009 Share Posted November 10, 2009 WHERE `EmployeeNumber`=34550 and `CategoryJobPosition`=1"; How many rows meet those conditions? Only 1. Quote Link to comment Share on other sites More sharing options...
surajkandukuri Posted November 10, 2009 Author Share Posted November 10, 2009 It has 2 rows for the WHERE CLAUSE, you can also see that in the database snapshot !!! Quote Link to comment Share on other sites More sharing options...
Mchl Posted November 10, 2009 Share Posted November 10, 2009 Sorry !! I didnot display it correctly EmployeeNumber Cat_Num CategoryJobPosition 34450 2 1 34550 1 1 This is my database !!! First row: EmployeeNumber = 34450 - does not match your conditions Second row: EmployeeNumber = 34550 - matches your conditions Number of rows that match your conditions: 1 Quote Link to comment Share on other sites More sharing options...
Maq Posted November 10, 2009 Share Posted November 10, 2009 It has 2 rows for the WHERE CLAUSE, you can also see that in the database snapshot !!! Maybe you're confused on what the logical operator "AND" means. It means that SQL will only return results that match both of the conditions. If you have two different ID's then how could they both possibly be returned? Quote Link to comment Share on other sites More sharing options...
gizmola Posted November 10, 2009 Share Posted November 10, 2009 Hi Maq, I have two rows for the same employeenumber , my table contains EMPLOYEENUMBER with multiple job positions so my table looks something like this EmployeeNumber JobPosition CategoryNumber 34550 1 1 34550 1 2 34550 1 5 My intention was to count the number of rows for EmployeeNumber for the each job position in this table so My intention result is 3 if go for 34550 and 1 Ok, so now that the "mystery" is resolved, I will opin about what you really wanted all along. SELECT EmployeeNumber, count(*) as countOf FROM cat_completiondate WHERE CategoryJobPosition = 1 GROUP BY EmployeeNumber, CategoryJobPosition Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.