Question 21650 in Mysql Question posted in
The official MySQL documentation can be found here.

How to get count in SQL if the column value is something specific – Mysql

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 query:

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
Tags:

3

Answers


  1. 0

    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';
    Login or Signup to reply.
  2. 0

    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'
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search