CODEWITHSUNDEEP
phpmysqlsql
Mech
Mech

Reputation: 4015

MySQL - Subtracting from separate queries

The goal is to subtract query 1 from query 2 and display it in a table. I can handle the 

while ($row = mysql_fetch_array($result)) {

portion of the request, but I can't get the following to work. It is meant to take this months sales from last months to see where they stand in comparison.

$result = mysql_query("SELECT (
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)
)
-
(
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
)");

Thanks in advance!

Upvotes: 0

Views: 66

Answers (3)

Jobayer
Jobayer

Reputation: 1231

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
)

Upvotes: 0

Barmar
Barmar

Reputation: 780974

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 missing GROUP 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

Upvotes: 2

Pathik Vejani
Pathik Vejani

Reputation: 4491

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

Upvotes: 0

Related Questions