How do I subract using one single statement in MySQL
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 >开发者_JAVA百科 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
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
SELECT SUM(Laptops) - COUNT(Laptops)
from ...
since your aggregate functions will be the last thing applied to the result of your GROUP
.
SELECT (SELECT query 1 ... ...) - (SELECT query 2 ... ...) AS 'value';
精彩评论