Nested Query returns nothing

Question

In my table (named checkout) in the database I have the

price (price of the item)
discount (what percentage of discount it had, eg 50 (for 50%))
howmany (which is the quantity of the product)
date (which is the current date)

I want my query to fetch the sum of the prices that had no discount on it and the sum of the prices that had discount on it for the year that we have and group it based on the month.

In the first SELECT I get the price multiplied by the quantity and it's without discount (works perfectly fine) but when I added the sub Query, the Query does not work. Any suggestions?

The (price*(1-(discount/100) in the Sub Query, first gets the discount for example 30/100=0.3 then it substracts it from the 1 (1-0.3=0.7 ) then I multiplied it with the price (price*0.7) and it returns the price with the discount

$sqlQuery = "SELECT sum(price*howmany) as price, 
             (SELECT sum((price*(1-(discount/100))*howmany) as price 
                    FROM checkout 
                    WHERE '$year'=YEAR(date) AND discount IS NOT NULL
                    GROUP BY Month(date) 
                    ORDER BY Month(date)) as discountPrice 
             FROM checkout 
             WHERE '$year'=YEAR(date) AND discount IS NULL
             GROUP BY Month(date) 
             ORDER BY Month(date) ";

You should get an error since sub query likely returns more than 1 row. — P.Salmon, Feb 11 '22 at 16:04
@P.Salmon so there is no way of getting discounted and non discounted without nested query? — john, Feb 11 '22 at 16:27

score 0 · Answer 1 · answered Feb 11 '22 at 16:52

you don't need a subquery. You can put the discount condition inside the SUM() calls.

SELECT 
    MONTH(date) AS month,
    SUM(IF(discount IS NULL, price, 0) * howmany) AS price,
    SUM(IF(discount IS NOT NULL, price*(1-(discount/100))*howmany, 0) AS discountPrice
FROM checkout
WHERE YEAR(date) = $year
GROUP BY month
ORDER BY month

See need to return two sets of data with two different where clauses

Nested Query returns nothing

1 Answers1