php - MySQL - Subtracting from separate queries

Solution:

You can't use a row of data as a value in an expression. You can only use a subquery as a value if it returns a single element (1 row containing one column). For what you're doing, you need to join the subqueries and then subtract each column.

I think you're also missingGROUP BY User in your queries; it makes little sense to select that column if you're not calculating the totals by user.

SELECT lastmonth.User, 
       lastmonth.COMPUTERSTotal - lastday.COMPUTERSTotal AS COMPUTERSTotal,
       lastmonth.LAPTOPCOMPUTERSTotal - lastday.COMPUTERSTotal AS LAPTOPCOMPUTERSTotal,
       ...
FROM (Select
        User, 
        SUM(COMPUTERS) as COMPUTERSTotal,
        SUM(LAPTOPCOMPUTERS) as LAPTOPCOMPUTERSTotal,
        SUM(PCCOMPUTERS) as PCCOMPUTERSTotal,
        SUM(KEYBOARDSUnits) as KEYBOARDSTotal, 
        SUM(MOUSEUnits) as MOUSETotal, 
        SUM(PRINTERSUnits) as PRINTERSTotal, 
        SUM(MISCUnits) as MISCTotal,
        SUM(PACKAGES) as PACKAGESTotal,
        (round(SUM(LAPTOPCOMPUTERS) / SUM(COMPUTERS) * 100,2)) as LAPTOPPercentage,
        (round(SUM(PACKAGES) / SUM(PCCOMPUTERS) * 100,2)) as PACKAGEPercentage,
        SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) as PERIPHERALTotal,
        round(SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) / SUM(COMPUTERS),2) as PERIPHERALPERSALE,
        ((SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) / SUM(COMPUTERS) * (SUM(COMPUTERS) * 1)) * (round(SUM(LAPTOPCOMPUTERS) / SUM(COMPUTERS) * 100,2))) as Ratio
    FROM my_db
    WHERE YEAR(`Created`) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
    AND MONTH(`Created`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
    GROUP BY User
    ) AS lastmonth
JOIN (Select
        User,   
        SUM(COMPUTERS) as COMPUTERSTotal,
        SUM(LAPTOPCOMPUTERS) as LAPTOPCOMPUTERSTotal,
        SUM(PCCOMPUTERS) as PCCOMPUTERSTotal,
        SUM(KEYBOARDSUnits) as KEYBOARDSTotal, 
        SUM(MOUSEUnits) as MOUSETotal, 
        SUM(PRINTERSUnits) as PRINTERSTotal, 
        SUM(MISCUnits) as MISCTotal, 
        SUM(PACKAGES) as PACKAGESTotal,
        (round(SUM(LAPTOPCOMPUTERS) / SUM(COMPUTERS) * 100,2)) as LAPTOPPercentage,
        (round(SUM(PACKAGES) / SUM(PCCOMPUTERS) * 100,2)) as PACKAGEPercentage,
        SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) as PERIPHERALTotal,
        round(SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) / SUM(COMPUTERS),2) as PERIPHERALPERSALE,
        ((SUM(KEYBOARDSUnits + MOUSEUnits + PRINTERSUnits + MISCUnits) / SUM(COMPUTERS) * (SUM(COMPUTERS) * 1)) * (round(SUM(LAPTOPCOMPUTERS) / SUM(COMPUTERS) * 100,2))) as Ratio
    FROM my_db
    WHERE `Created` >= CURDATE() - INTERVAL DAY(NOW()) - 1 DAY
    GROUP BY User
    ) AS lastday
ON lastmonth.User = lastday.User
Copy code

Solution:

First use alias for both the queries. Then run one query that subtracts those queries.

Solution:

You need to use individual select query for all data, now you have used only one select statement, you need to rewrite it like this -SELECT( Select num1 FROM table1 ) - ( Select num1 FROM table2
),( Select num2 FROM table1 ) - ( Select num2 FROM table2
)