count multiple row values in the same query, mysql

Question

table Devices:

id   device type_id device_status
1     dev1     2        1         
2     dev2     2        2        
3     dev3     7        2         
4     dev5     9        3

models table:

model_id vendor_id model_name
  1        3          name1          
  2        5          name2       
  3        7          name3         
  4        7          name4

and query looks this:

 SELECT model_id, type_id, model_name,
 COUNT(type_id) AS numOfDevices
 FROM devices
 LEFT JOIN models ON models.model_id = devices.type_id
 WHERE device_status = 2 OR device_status = 3
 GROUP BY type_id

what I am getting from that query is:

There are $numOfDevices $model_name items.

and this is OK, but is there a way to count devices only with device_status =3 in the same query?

Arion · Accepted Answer · 2012-05-02 11:53:43Z

3

Maybe something like this:

SELECT model_id, type_id, model_name,
 SUM(CASE WHEN device_status=2 THEN 1 ELSE 0 END) AS device2,
 SUM(CASE WHEN device_status=3 THEN 1 ELSE 0 END) AS device3,
 SUM(CASE WHEN device_status IN (2,3) THEN 1 ELSE 0 END) AS device2device3
 FROM devices
 LEFT JOIN models ON models.model_id = devices.type_id
 WHERE device_status = 2 OR device_status = 3
 GROUP BY type_id

edited May 2, 2012 at 11:53

answered May 2, 2012 at 11:01

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Robin Castlin Over a year ago

SUM(device_status=2) AS device2 don't make it hardier then it is :) The "boolean" returns either 0 or 1, so your CASE WHEN isn't required.

InTry Over a year ago

i am getting error: {"errorInfo":["42000",1064,"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM devices\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN models ON models.model_id = devices.type_id\r' at line 4"]}. BTW I'm using PDO and 5.1.62 is the version of mysql

InTry Over a year ago

that's fixed error, but (i am sorry) result is not what I really need, I need them bout together (dev3+dev2) + dev3 separatly... i tried SUM(CASE WHEN device_status=2 AND device_status=3 THEN 1 ELSE 0 END) but it doesn't work

InTry Over a year ago

OR is the right one: SUM(CASE WHEN device_status=2 OR device_status=3 THEN 1 ELSE 0 END)

Arion Over a year ago

@InTry : Updated then answer with a case for both dev3+dev2.

|

Collectives™ on Stack Overflow

count multiple row values in the same query, mysql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related