asked
virtual count varible use in where clause
This is my query: question: Get the list of members who has returned the book in time for at-least 3 times between May-2010 and June-2011. my ans: select COUNT(*) as mycount ,tblmember.memberName from tblmember,tblIssueReturn where tblIssueReturn.ExpectedReturnDt=tblIssueReturn.ReturnDate and (tblIssueReturn.ReturnDate between '2010/5/1' and '2011/6/1') group by memberName I want to check 'mycount' is greater than of 3 or not??? how to use the 'mycount' varible it is not working in where clause.
Checking out the [HAVING Clause](
http://msdn.microsoft.com/en-us/library/ms180199.aspx)
This query or something very close should get what you need. I will have to create a sample set of data to validate but it will be a few hours before I can do that. I have to be on a call that started two minutes ago. SELECT count (IR.memberid), M.MemberName FROM tblmember M JOIN tblIssueReturn IR on M.memberid = IR.memberid WHERE IR.ReturnDate between '2010/5/1' and '2011/6/1' GROUP BY IR.MemberID HAVING COUNT (*) >= 3
Your query needs some work before a good answer can be given but I think this is what your looking > select t.mycount, t.memberName from ( select COUNT(*) as mycount ,tblmember.memberName from tblmember join tblIssueReturn on tblIssueReturn.ExpectedReturnDt=tblIssueReturn.ReturnDate where (tblIssueReturn.ReturnDate between '2010/5/1' and '2011/6/1') ) t where t.mycount > 3 group by memberName
So then as TRAD said you need to actually provide us how the tables should be join if you want a good answer as I said above. I made an assumetion on the join I made is that the correct join "from tblmember join tblIssueReturn on tblIssueReturn.ExpectedReturnDt=tblIssueReturn.ReturnDate"
Try this one it also has the join that you gave a minute ago "select t.mycount, t.memberName from ( select COUNT(*) as mycount ,tblmember.memberName from tblmember join tblIssueReturn on tblIssueReturn.memberid=tblIssueReturn.memberid where (tblIssueReturn.ReturnDate between '2010/5/1' and '2011/6/1')group by tblmember.memberName ) t where t.mycount > 3 group by memberName "
There are a couple of problems with your query. First of all, you are doing a cross join between tblMember and tblIssueReturn. Say you have 100 "issue return" records (whatever those are...) and 50 of them match your criteria (between May 1 and June 1 of 2010, and the expected return date is equal to the return date). Then, say you have 10 members. In this case, you will get 500 matches. You probably don't mean to do this--perhaps you just forgot the relationship between tblMember and tblIssueReturn in your query?--but this will result in your query doing something you don't expect it to do. This leads me to note #2, which is kind of a problem: you're using a deprecated method for joins. It is highly recommended that you explicitly name your joins. Instead of from tblmember, tblIssueReturn where tblmember.something = tblIssueReturn.something You would have: from tblmember inner join tblIssueReturn on tblmember.something = tblIssueReturn.something This prevents you from creating accidental cross joins. Getting a little more niggling, I would recommend using the ISO format for dates in SQL Server: instead of '2010/5/1', it's better to use '2010-05-01' because there is no ambiguity. If you run '2010/5/1' on a database with, say, a French collation, it will not work the way you think it should. Getting to the main question that you have, @TRAD is right: you need a HAVING clause (and his code just had an incorrect GROUP BY). Think of HAVING versus WHERE like this: you use WHERE when you want to filter out **elements**, but you use HAVING when you want to filter out **groups which have already been aggregated**. Your currently broken query (because it is doing a cross join) would then look like: select COUNT(*) as mycount, tblmember.memberName from tblmember, tblIssueReturn where tblIssueReturn.ExpectedReturnDt = tblIssueReturn.ReturnDate and tblIssueReturn.ReturnDate between '2010/5/1' and '2011/6/1' group by memberName having COUNT(*) > 3; But again, it is a broken query: you'll need to fix the cross join before it returns the correct values.
I have tried it with myself and i got the answer.. As u all talked about joins.. i am not comfortable with join.. as i m in intial stage of learning sql. MY ANSWER IS select COUNT(MemeberId) from tblIssueReturn where ReturnDate <= ExpectedReturnDt and YEAR(IssueDate) = 2011 and MONTH(IssueDate) in(1, 5, 6) group by MemeberId having COUNT(MemeberId) > 0 Thank you for all the sugg u gave.
