How do I subract using one single statement in MySQL

Question

How can I subtract the result of this query:

SELECT COUNT(Laptops) FROM (SELECT aaaUser.FIRST_NAME AS User,COUNT(workstation.WORKSTATIONNAME) AS Laptops FROM SystemInfo workstation 
LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID 
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID WHERE ( aaaUser.FIRST_NAME IS NOT NULL)  AND ISSERVER=0 
GROUP BY "User"
HAVING Laptops > 1
ORDER BY Laptops DESC) AS e

from this one:

SELECT SUM(Laptops) FROM (SELECT aaaUser.FIRST_NAME AS User,COUNT(workstation.WORKSTATIONNAME) AS Laptops FROM SystemInfo workstation 
LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID 
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID WHERE ( aaaUser.FIRST_NAME IS NOT NULL)  AND ISSERVER=0 
GROUP BY "User"
HAVING Laptops > 1
ORDER BY Laptops DESC) AS e

while only using a single query?

I am trying to create a report in a product that does not support running multiple queries - so temp tables are not an option.

Thanks in advance,

Ben

Answer 1

SELECT SUM(Laptops) - COUNT(Laptops)
from ...

since your aggregate functions will be the last thing applied to the result of your GROUP.

Answer 2

Unless I'm missing something, can't you just do this?

SELECT SUM(Laptops) - COUNT(Laptops)
FROM (SELECT aaaUser.FIRST_NAME AS User,COUNT(workstation.WORKSTATIONNAME) AS Laptops FROM SystemInfo workstation 
LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID 
LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID 
LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID 
LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID 
LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID WHERE ( aaaUser.FIRST_NAME IS NOT NULL)  AND ISSERVER=0 
GROUP BY "User"
HAVING Laptops > 1
ORDER BY Laptops DESC) AS e

Answer 3

SELECT (SELECT query 1 ... ...) - (SELECT query 2 ... ...) AS 'value';