Where clause with calculated column

Question

Hello i want to add a condition here like

where Date between ..

and

SalesPercentage > 50

but it salespercentage is not a column in db and its just casted like in the code below.

How can i use it. Thanks in advance.

      var sql = @"SELECT TOP 10 

                    sp.ProductName, 

                    SUM(sp.QtySold) AS QtySold,

                    SUM(r.QtyinPieces) AS StockLimit,                  

                    CAST(    (CAST    (SUM(sp.QtySold) AS FLOAT)     /    CAST(SUM(r.QtyinPieces) AS FLOAT)  ) * 100 AS DECIMAL(8,2)       ) [SalesPercentage]

                FROM 

                    Sales_productholder sp

                    JOIN Orders_productholder r ON (sp.ProductID = r.ProductID)



                GROUP BY 

                    sp.ProductName, r.ProductID, r.QtyinPieces

                ORDER BY 

                    SUM(sp.QtySold) DESC";

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

You can add HAVING like this.

HAVING
    CAST( (CAST ( SUM(sp.QtySold) AS FLOAT) / CAST(SUM(r.QtyinPieces) AS FLOAT) ) * 100 AS DECIMAL(8,2) ) > 50

All query:

SELECT TOP 10 
    sp.ProductName, 
    SUM(sp.QtySold) AS QtySold,
    SUM(r.QtyinPieces) AS StockLimit,                  
    CAST( (CAST ( SUM(sp.QtySold) AS FLOAT) / CAST(SUM(r.QtyinPieces) AS FLOAT) ) * 100 AS DECIMAL(8,2) ) [SalesPercentage]
FROM 
    Sales_productholder sp
    JOIN Orders_productholder r ON (sp.ProductID = r.ProductID)
WHERE [Date] between @DateBegin and @DateAdd
GROUP BY 
    sp.ProductName, r.ProductID, r.QtyinPieces
HAVING
    CAST( (CAST ( SUM(sp.QtySold) AS FLOAT) / CAST(SUM(r.QtyinPieces) AS FLOAT) ) * 100 AS DECIMAL(8,2) ) > 50
ORDER BY 
    SUM(sp.QtySold) DESC

Where clause with calculated column

Sign up