0

I need to get the count of a column for a particular id, but only when the column value is "Yes". Following is wrong i guess. mid(PRIMARY KEY)

SELECT COUNT(mt.currentstate = 'Yes') AS currentstate FROM mytable mt WHERE mid = 100 GROUP BY mid;

For the COUNT value only i need from the column mt.currentstate = 'Yes', but for the ResultSet I need to get all the values where mt.currentstate = 'Yes' or 'No'

Improve this question
5
  • 1
    I think mt.currentstate = 'Yes' should be in where clause. Commented Oct 16, 2013 at 5:48
  • Now If i put mt.currentstate = 'Yes' inside WHERE clause, it will filter all the rows where mt.currentstate = 'Yes', but I need other column values from the rows mt.currentstate = 'No'..What I'm gonna do? Commented Oct 16, 2013 at 5:55
  • @Axel answered what you looking for Commented Oct 16, 2013 at 5:58
  • 1
    "The COUNT value only i need from the column mt.currentstate = 'Yes', but for the ResultSet I need to get all the values where mt.currentstate = 'Yes' or 'No'" - Then you have to make two queries. One aggregates for currentstate = 'Yes', and one gives you all rows independent of currentstate. Or could you provide an example of the output you expect to make it clear? Commented Oct 16, 2013 at 6:22
  • @Axel Yes Axel, I guess "Then you have to make two queries. One aggregates for currentstate = 'Yes'..." suggestion is correct, I followed that way, Thanks a lot for the help.. Commented Oct 16, 2013 at 6:59

3 Answers 3

Reset to default
1

You can try below query, it will work if i'm not worng.

SELECT COUNT(mt.mid) AS counter FROM mytable mt WHERE mid = 100 AND mt.currentstate = "YES" GROUP BY mid;

Thanks!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I think you have to remove the GROUP BY part because the result doesn't include mid.
Yes, may be. but actual question is how to get total count using this condition. but yes you are also right. +1
Now If i put mt.currentstate = 'Yes' inside WHERE clause, it will filter all the rows where mt.currentstate = 'Yes', but I need other column values from the rows mt.currentstate = 'No'..What I'm gonna do?
@DilukshanMahendra See the dit to my answer. Does it work for you?
1

Try this: 

SELECT COUNT(*) AS currentstate FROM mytable WHERE mid = 100 AND currentstate='Yes';

EDIT: "Now If i put mt.currentstate = 'Yes' inside WHERE clause, it will filter all the rows where mt.currentstate = 'Yes', but I need other column values from the rows mt.currentstate = 'No'..What I'm gonna do?"

SELECT currentstate, COUNT(*) AS c FROM mytable WHERE mid = 100 GROUP BY currentstate;
Improve this answer

4 Comments

Now If i put mt.currentstate = 'Yes' inside WHERE clause, it will filter all the rows where mt.currentstate = 'Yes', but I need other column values from the rows mt.currentstate = 'No'..What I'm gonna do?
BTW: You should give more information on the table definition like can there be more than one rows for a unique mid? What are the table keys?
I don't think your latest updated query will work as per what @DilukshanMahendra required.
@Chandresh Yes, if mid is a primary key, it won't work as expected. But the real problem is that it's hard to tell what is expected...
0 
SELECT COUNT(mt.mid) AS currentstate FROM mytable mt WHERE mid = 100 and mt.currentstate = 'Yes' GROUP BY mid;
Improve this answer

1 Comment

Now If i put mt.currentstate = 'Yes' inside WHERE clause, it will filter all the rows where mt.currentstate = 'Yes', but I need other column values from the rows mt.currentstate = 'No'..What I'm gonna do?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.