Systems Management

Are all of the labels in question in a group? We have labels assigned for departments and they are all in a "Departments" group. The following report shows the number of computers in each department:

 SELECT DISTINCT(LABEL.NAME) AS DEPARTMENT,
COUNT(MACHINE.NAME) AS COUNT
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
LEFT JOIN LABEL_LABEL_JT ON (LABEL.ID = LABEL_LABEL_JT.CHILD_LABEL_ID AND LABEL_LABEL_JT.LABEL_ID = 258)
WHERE LABEL_LABEL_JT.LABEL_ID = 258
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT

To make this work for your label group you'll need to find the ID of the label group and replace the 258 with your ID.

If the labels aren't in a group then it is still possible but the SQL will be a little different. You could do something like this:

 SELECT DISTINCT(LABEL.NAME) AS DEPARTMENT,
COUNT(MACHINE.NAME) AS COUNT
FROM MACHINE  
LEFT JOIN MACHINE_LABEL_JT ON (MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID)  
LEFT JOIN LABEL ON (LABEL.ID = MACHINE_LABEL_JT.LABEL_ID  AND LABEL.TYPE <> 'hidden') 
WHERE LABEL.NAME in ("English", "Academic Advising", "Advancement Services", "Athletics")
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT

Enter the label names you want included in the report in the WHERE LABEL.NAME in ("label1", "label2")

Try this. Edit to match your label names.

SELECT COUNT(M.NAME) 'Machine Count', L.NAME as 'Label Name'

FROM MACHINE M

LEFT JOIN MACHINE_LABEL_JT MLJT ON M.ID=MLJT.MACHINE_ID

LEFT JOIN LABEL L ON L.ID = MLJT.LABEL_ID

WHERE L.NAME LIKE '<LABEL_1>%'

OR L.NAME LIKE '<LABEL_2>%'

OR L.NAME LIKE '<LABEL_3>%'

GROUP BY L.NAME

ORDER BY L.NAME