CODEWITHSUNDEEP
mysqlsql
Goover
Goover

Reputation: 410

Mysql SELECT COUNT(COL) with a condition

I have a table with some data like

id      group_id    f1    f2    f3
1          1        a     b   
2          1        c   
3          2        a           c

How can i retrieve one row with group_id and count of rows for each field satisfying some textual condition?

Like that:

MY_MAGIC_SELECT(`f1`='a',`f3`='c');

must return

group_id    f1    f2    f3
   1        1     0     0        
   2        1     0     1

Upvotes: 5

Views: 11989

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270607

Using a sequence of SUM(CASE...) aggregate functions to represent each of your conditions should do it. The CASE returns a 0 or 1 if the condition is matched, and the SUM() adds the result. The GROUP BY is applied on the group_id.

SELECT
  group_id
  SUM(CASE WHEN f1 = 'a' THEN 1 ELSE 0 END) AS f1,
  SUM(CASE WHEN f2 = 'b' THEN 1 ELSE 0 END) AS f2,
  /* f3 = 'b' isn't in your "magic select" but here it is anyway... */
  SUM(CASE WHEN f3 = 'c' THEN 1 ELSE 0 END) AS f3
FROM 
  yourtable
GROUP BY group_id

Specifically for MySQL, you don't need the CASE since the boolean expression f1 = 'a' will itself return a 1 or 0. So you can simplify it to the example below. This is not portable to any RDBMS, however.

SELECT
  group_id
  SUM(f1 = 'a') AS f1,
  SUM(f2 = 'b') AS f2,
  SUM(f3 = 'c') AS f3
FROM 
  yourtable
GROUP BY group_id

Here is a quick demonstration on SQLfiddle.com

Upvotes: 14

Andrew
Andrew

Reputation: 7768

Well, it is simple MySQL and you need to learn this. Please check this out: http://www.w3schools.com/sql/sql_where.asp

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

SELECT column_name(s)
 FROM table_name
 WHERE column_name operator value

for example

SELECT * FROM Persons
 WHERE City='Sandnes'

Upvotes: -2

Related Questions