How to get count in SQL if the column value is something specific-CodePudding

I want to show the count in the SQL query, but I have a problem with it. I want to search for count only if there is something specific in the value column. Here is an example of my code: SELECT COUNT(IF status='F') FROM relation WHERE from='7'

So, here I want to get the amount of "relation" from the column "status" from the table, when the status value is F.

With the above code I get an error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`status`='F') FROM `relation` WHERE `from`='7' LIMIT 0, 25' at line 1

Thanks for your helping.

CodePudding user response：

I think two common ways are:

SELECT 
 COUNT(CASE WHEN status='F' THEN 1 END) 
FROM relation
WHERE from='7'

and

SELECT
 SUM(CASE WHEN status='F' THEN 1 ELSE 0 END)
FROM relation
WHERE from='7'

The first one uses count but since it only counts non-null values, it only counts the ones you want. The second uses sum, but since we sum 1 if your condition is true and 0 if your condition is false it's equivalent to counting where the condition is true.

Although in your case since you're not doing a group by you could just use

SELECT
 COUNT(*)
FROM relation
WHERE from='7' AND status='F'

CodePudding user response：

You can count this way to get only counts where the value of status is F

SELECT COUNT(*) FROM relation WHERE from='7' AND status='F';

CodePudding user response：

I prefer summing a boolean expression:

SELECT SUM(status = 'F') AS cnt
FROM relation
WHERE `from` = '7';

Note that FROM is a reserved MySQL keyword. If you really did name a column FROM, then you will have to forever escape it in backticks. You should avoid naming your database objects using reserved keywords.

CodePudding user response：

Thanks for your quick replies today, this helped me a lot.