How to count different values in a same column in mysql?

Question

i have a table like this

id(pk)|product_id(fk)|serial(varchar)|status(varchar)
  1   |    2         |      e098     | sold
  2   |    2         |      e008     | faulty

what i need to extract is:

array(faulty=>1,sold=>1)

i i can do it with a complex query, is there any SIMPLE query which can help? i tried this: SELECT COUNT(id) as product_details.status FROM product_details WHERE product_id=2 GROUP BY status which didn't work.

Answer 1

(ab)Use mysql's auto-typecasting:

SELECT SUM(status='sold') AS sold, SUM(status='faulty') AS faulty
FROM ...

the boolean result of status='sold' will be typecast to integer 0/1 and then summed up.

The other option is just to do a regular query and then do the pivoting in client-side code:

SELECT id, status, COUNT(status) AS count
FROM ...
GROUP BY id, status

and then

while(... fetch ...) {
   $results[$row['id']][$row['status']] = $row['count'];
}

Answer 2

You want to query status as well like this.

SELECT status, COUNT(id) as count FROM product_details WHERE product_id=2 GROUP BY status