CODEWITHSUNDEEP
phpmysqlsql
KMS
KMS

Reputation: 19

Sql select count use five condion

i count to some row form my database in five condition use the sql query it's work with 0 if i put the number more than 0 then it not show any result. please help . it's work with script 

$query33=mysqli_query($con,"SELECT COUNT(CASE WHEN ms > 0 THEN 1 ELSE NULL END) && (CASE WHEN ts > 0 THEN 1 ELSE NULL END) && (CASE WHEN has > 0 THEN 1 ELSE NULL END) && (CASE WHEN gs > 0 THEN 1 ELSE NULL END) && (CASE WHEN ggs > 0 THEN 1 ELSE NULL END)  AS mtnjoco33 from mainmar19 where  month='$month' and taluk='$taluk';");
$result133=mysqli_fetch_array($query33);
$mtnjoco33=$result133['mtnjoco33'];

and not work if i put the condition number

$query33=mysqli_query($con,"SELECT COUNT(CASE WHEN ms > 0 THEN 1 ELSE NULL END) && (CASE WHEN ts > 0 THEN 1 ELSE NULL END) && (CASE WHEN has > 0 THEN 1 ELSE NULL END) && (CASE WHEN gs > 5 THEN 1 ELSE NULL END) && (CASE WHEN ggs > 5 THEN 1 ELSE NULL END) && (CASE WHEN tmis > 8 THEN 1 ELSE NULL END)  AS mtnjoco33 from mainmar19 where  month='$month' and taluk='$taluk';");
$result133=mysqli_fetch_array($query33);
$mtnjoco33=$result133['mtnjoco33'];

my databse https://i.sstatic.net/Olyx0.png

Upvotes: 0

Views: 56

Answers (1)

GMB
GMB

Reputation: 222492

You seem to be trying to count how many records satisfy all the conditions. If so, then your query can be simplified as follows:

SELECT SUM( ms > 0 AND ts > 0 AND has > 0 AND gs > 0 AND ggs > 5 AND tmis > 8) AS mtnjoco33 
FROM mainmar19 
WHERE month='$month' and taluk='$taluk'

The problem with your original code is that COUNT() only surrounds the first CASE expression, not the rest of the conditions; this makes the query inconsistent.

Also, please note that operator && is a non-standard MySQL extension that has been deprecated in recent versions.

Upvotes: 2

Related Questions