Clever Geek Handbook

Any way to refer to MySQL "AS" values ​​in a query?

in the next query, I summarize all the sales items of all sales in the given range. Included in this summary is "sumOfCost" and "sumOfPrice", I would like "sumOfProfit" too. The problem is that since they are only "AS" variables, I'm not sure how to reference them and my attempt at "... Blah AS blah, sumOfPrice - sumOfCost = sumOfProfit ..." unfortunately doesn't work

Here is my request:

SELECT  Sales.SaleID, 
        Sales.StaffID, 
        Sales.CustomerID, 
        Sales.Timestamp, 
        Sales.Refunded, 
        Sales.PaymentType, 
        Staff.Forename AS staffForename, 
        Staff.Surname AS staffSurname, 
        (   
            SELECT GROUP_CONCAT(Quantity, ' x ', Name) 
            FROM SaleItems 
            WHERE SaleItems.SaleID = Sales.SaleID
        ) AS itemList, 
        (
            SELECT sum(Cost*Quantity) 
            FROM SaleItems 
            WHERE SaleItems.SaleID = Sales.SaleID
        ) AS sumOfCost, 
        (
            SELECT sum(Price*Quantity) 
            FROM SaleItems 
            WHERE SaleItems.SaleID = Sales.SaleID
        ) AS sumOfPrice 
FROM    Sales 
        INNER JOIN Staff 
            ON Sales.StaffID = Staff.StaffID 
WHERE   Sales.Deleted = '0' 
ORDER   BY Timestamp DESC 
LIMIT   0, 15

Sorry for the length of the request, I tried my best to optimize it, but right now I'm looking for a solution to this problem.

Thank you in advance:)

+3


source to share


1 answer


One way to solve this problem is to wrap the whole query in a subquery,

SELECT  *,
        sumOfCost - sumOfPrice AS sumOfProfit
FROM
    (
        SELECT  Sales.SaleID, 
                Sales.StaffID, 
                Sales.CustomerID, 
                Sales.Timestamp, 
                Sales.Refunded, 
                Sales.PaymentType, 
                Staff.Forename AS staffForename, 
                Staff.Surname AS staffSurname, 
                (   
                    SELECT GROUP_CONCAT(Quantity, ' x ', Name) 
                    FROM SaleItems 
                    WHERE SaleItems.SaleID = Sales.SaleID
                ) AS itemList, 
                (
                    SELECT sum(Cost*Quantity) 
                    FROM SaleItems 
                    WHERE SaleItems.SaleID = Sales.SaleID
                ) AS sumOfCost, 
                (
                    SELECT sum(Price*Quantity) 
                    FROM SaleItems 
                    WHERE SaleItems.SaleID = Sales.SaleID
                ) AS sumOfPrice 
        FROM    Sales 
                INNER JOIN Staff 
                    ON Sales.StaffID = Staff.StaffID 
        WHERE   Sales.Deleted = '0' 
        ORDER   BY Timestamp DESC 
        LIMIT   0, 15
    ) s

By the way, they are called ALIAS

.

The reason ALIAS

it cannot be used to calculate at the same level where they were defined is because the server is executing the offer FROM

before the offer SELECT

. ALIAS

are in the sentence SELECT

, here's the full SQL execution order:

  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • SELECT clause
  • ORDER BY clause


For better performance, I would rather use JOIN

than subqueries

.

UPDATE 1

SELECT  Sales.SaleID, 
        Sales.StaffID, 
        Sales.CustomerID, 
        Sales.Timestamp, 
        Sales.Refunded, 
        Sales.PaymentType, 
        Staff.Forename AS staffForename, 
        Staff.Surname AS staffSurname, 
        COALESCE(a.itemList, '') itemList,
        COALESCE(b.sumOfCost, 0) sumOfCost, 
        COALESCE(c.sumOfPrice, 0) sumOfPrice,
        COALESCE(b.sumOfCost, 0) - COALESCE(c.sumOfPrice, 0) AS sumOfProfit
FROM    Sales 
        INNER JOIN Staff 
            ON Sales.StaffID = Staff.StaffID 
        LEFT JOIN 
        (   
            SELECT SaleID, GROUP_CONCAT(Quantity, ' x ', Name) itemList
            FROM SaleItems 
            GROUP BY SaleID
        ) a ON a.SaleID = Sales.SaleID
        LEFT JOIN
        (
            SELECT SaleID, sum(Cost*Quantity) sumOfCost
            FROM SaleItems 
            GROUP BY SaleID
        ) b ON b.SaleID = Sales.SaleID
        LEFT JOIN
        (
            SELECT SaleID, sum(Price*Quantity) sumOfPrice 
            FROM SaleItems 
            GROUP BY  SaleID
        ) c ON c.SaleID = Sales.SaleID
WHERE   Sales.Deleted = '0' 
ORDER   BY Timestamp DESC 
LIMIT   0, 15

UPDATE 2

SELECT  Sales.SaleID,
        Sales.StaffID,
        Sales.CustomerID,
        Sales.TIMESTAMP,
        Sales.Refunded,
        Sales.PaymentType,
        Staff.Forename AS staffForename,
        Staff.Surname AS staffSurname,
        COALESCE(a.itemList, '') itemList,
        COALESCE(a.sumOfCost, 0) sumOfCost,
        COALESCE(a.sumOfPrice, 0) sumOfPrice,
        COALESCE(a.sumOfCost, 0) - COALESCE(a.sumOfPrice, 0) AS sumOfProfit
FROM    Sales
        INNER JOIN Staff
            ON Sales.StaffID = Staff.StaffID
        LEFT JOIN
        (  
            SELECT      SaleID,
                        GROUP_CONCAT(Quantity, ' x ', Name) itemList,
                        SUM(Cost*Quantity) sumOfCost,
                        SUM(Price*Quantity) sumOfPrice
            FROM SaleItems
            GROUP BY SaleID
        ) a ON a.SaleID = Sales.SaleID
WHERE   Sales.Deleted = '0'
ORDER   BY TIMESTAMP DESC
LIMIT   0, 15
+2


source






More articles:


All Articles