I am new to SQL Server. I want to equal two columns using sum function. Please find the below query.
select top 1000
o.orderamount, sum(oi.amount), oi.orderid
from
orders o
inner join
orderitem oi on o.orderid = oi.orderid
where
orderamount = sum(oi.amount)
group by
oi.orderid, orderamount
order by
oi.orderid desc
Whenever I execute this code, I get an error.
Please help me.
You can just use HAVING:
select top 1000 o.orderamount, sum(oi.amount), oi.orderid
from orders o
inner join orderitem oi on o.orderid = oi.orderid
group by oi.orderid ,orderamount
HAVING orderamount = sum(oi.amount)
order by oi.orderid desc
You can use this :
select top 1000 o.orderamount, oi.amount, oi.orderid
from orders o
inner join (select orderid, sum(amount) amount from orderitem group by orderid) orderitem
oi on o.orderid = oi.orderid and orderamount = oi.amount
order by oi.orderid desc
Try using HAVING instead of WHERE clauses in sum queries. Also: add indentation and UPPER CASE for readability
SELECT TOP 1000 o.OrderAmount, sum(oi.Amount), oi.OrderID
FROM Orders o
INNER JOIN OrderItem oi ON oi.OrderID = o.OrderID
GROUP BY oi.OrderID, OrderAmount
HAVING OrderAmount = sum(oi.Amount)
ORDER BY oi.OrderID DESC;
