Counting computers for each lab

Question

Alright I have a problem with having to count PCs, and Macs from different labs. In each lab I need to display how many PC and Macs there is available. The data is coming from a SQL server, right am trying sub queries and the use of union, this the closest I can get to what I need.

The query below shows me the number of PCs, and Macs in two different columns, but of course, the PCs will be in one row and the Macs on another right below it. Having the lab come up twice.

EX:

LabName --  PC  /  MAC

Lab1  --     5   /   0

Lab1   --    0    /  2

Query

SELECT 
Labs.LabName, COUNT(*),0 AS Mac


FROM
HardWare INNER JOIN Labs ON HardWare.LabID = Labs.LabID

WHERE ComputerStatus = 'AVAILABLE'

GROUP BY Labs.LabName

UNION

SELECT
Labs.LabName, COUNT(*), (SELECT COUNT(Manufacturer)) AS Mac

FROM 
HardWare INNER JOIN Labs ON HardWare.LabID = Labs.LabID

WHERE ComputerStatus = 'AVAILABLE' AND Manufacturer = 'Apple'

GROUP BY Labs.LabName

ORDER BY Labs.LabName

So is there any way to get them together in one row as in

Lab1 -- 5 / 2

or is there a different way to write the query?

anything will be a big help, am pretty much stuck here.

Cheers

Answer 1

Simple from top of the head (not checked), may need some syntax fixing.

SELECT Labs.LabName, 
SUM (CASE WHEN Manufacturer = 'Apple' THEN 1 ELSE 0 END) AS 'MAC',
SUM (CASE WHEN Manufacturer != 'Apple' THEN 1 ELSE 0 END) AS 'PC'

FROM HardWare 
INNER JOIN Labs ON HardWare.LabID = Labs.LabID
WHERE ComputerStatus = 'AVAILABLE'
GROUP BY Labs.LabName
ORDER BY Labs.LabName

Answer 2

The general sub-query syntax to achieve what you're looking for may be along these lines:

Select 
    labName, 
    (SELECT 
        COUNT(*) 
    FROM hardware 
    WHERE
        hardware.lab = lab.id
        AND type = 'pc'
        AND status = 'available'
    ) as PC, 
    (SELECT 
        COUNT(*) 
    FROM hardware 
    WHERE
        hardware.lab = lab.id
        AND type = 'mac'
        AND status = 'available'
    ) as Mac,
FROM
    labs