CODEWITHSUNDEEP
sqlsql-server
KishorK
KishorK

Reputation: 13

SQL SUM of a Column in WHERE Clause

I got a large table like below :

--------------------------
OrderNo     OrderQuantity
--------------------------
100             250.00
101             455.10
102              89.55
103             112.15
104             330.12

I have to filter by SUM of OrderQuantity by less than or equal to 800.00. My SQL query is below which did not work as expected.

SELECT OrderNo, OrderQuantity
    FROM WorkOrder
        HAVING SUM(OrderQuantity) <= 800

I expect a result like below. 

--------------------------
OrderNo     OrderQuantity
--------------------------
100             250.00
101             455.10
102              89.55

Can anybody help me please?

Upvotes: 0

Views: 12288

Answers (5)

MarkD
MarkD

Reputation: 1063

if you are using sql sever 2012 or later you could do the following

SELECT OrderNo, OrderQuantity
FROM WorkOrder
Group By OrderNo, OrderQuantity
Having SUM(OrderQuantity) OVER (ORDER BY OrderNo) < 800

Upvotes: 0

user7715598
user7715598

Reputation: 

;With cte(OrderNo,OrderQuantity)
AS
(
SELECT 100,250.00 Union all
SELECT 101,455.10 Union all
SELECT 102, 89.55 Union all
SELECT 103,112.15 Union all
SELECT 104,330.12 
)
SELECT OrderNo,OrderQuantity From
(
SELECT *,SUM(OrderQuantity) Over( Order by OrderNo) AS SumOrderQuantity from cte
)Dt
Where Dt.SumOrderQuantity <=800

OutPut

--------------------------
OrderNo     OrderQuantity
--------------------------
100             250.00
101             455.10
102              89.55

Upvotes: 1

StickT
StickT

Reputation: 1

you're doing this in the wrong way , bad grammer , when you're using a having clause ,you should group by the columns you want use in having clause,like this: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value ,try like this ,you may solve this problem

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

One approach is to use a correlated subquery to find the cumulative sum of the order quantity, and then restrict to only records where that sum is less than or equal to 800.

SELECT
    t1.OrderNo,
    t1.OrderQuantity,
    (SELECT SUM(t2.OrderQuantity) FROM WorkOrder t2
     WHERE t2.OrderNo <= t1.OrderNo) AS CumOrderQuantity
FROM WorkOrder t1
WHERE t1.CumOrderQuantity <= 800

Upvotes: 0

Utsav
Utsav

Reputation: 8093

Use this. Although your sample has only 1 orderno per orderquantity, if you have multiple orderno, and you want to get the sum, use the below query.

SELECT OrderNo, sum(OrderQuantity)
    FROM WorkOrder
GROUP BY OrderNo
HAVING SUM(OrderQuantity) <= 800

So if you want to perform any aggregated calculations on a set of rows, you need GROUP BY

Upvotes: 0

Related Questions