StudentDev StudentDev - 3 years ago 168
SQL Question

Where clause with calculated column

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";

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download