CODEWITHSUNDEEP
sqlsql-server
daniel damianov
daniel damianov

Reputation: 49

Find the sum of all quantities per item

I use the NorthWind database and my point is to select the total amount of shipped orders per City in one row. Sorry for dummy question but I am new in Sql

select e.FirstName , o.ShipCity, od.UnitPrice,Sum(od.Quantity), 
sum(od.Quantity*od.UnitPrice) from [Order Details] as od 
inner join Products as p on p.ProductID=od.ProductID
inner join Orders as o on o.OrderID =od.OrderID
inner join Employees as e on e.EmployeeID=o.EmployeeID
where o.ShipCity= 'Graz' and o.isCanceled !=1 and e.FirstName ='Nancy'
group by e.FirstName , o.ShipCity,od.UnitPrice

enter image description here

Upvotes: 0

Views: 595

Answers (2)

dnoeth
dnoeth

Reputation: 60462

To get the amount per city you must remove FirstName and UnitPrice from your select.

select o.ShipCity,
   Sum(od.Quantity), 
   sum(od.Quantity*od.UnitPrice) 
from [Order Details] as od 
inner join Products as p on p.ProductID=od.ProductID
inner join Orders as o on o.OrderID =od.OrderID
inner join Employees as e on e.EmployeeID=o.EmployeeID
where o.ShipCity= 'Graz' and o.isCanceled !=1 and e.FirstName ='Nancy'
group by o.ShipCity

Upvotes: 0

EchoMike444
EchoMike444

Reputation: 1692

=> 

select e.firstName , o.ShipCity , sum ( od.Quantity) from [ order Details ] as od 
inner join Orders  as o on o.OrderId = od.OrderId
inner join Employees as e on e.employeeID=o.EmployeeID
where o.shipcity = 'Graz'and o.iscanceled !=1 and e.firstname='Nancy'
group by e.firstName , o.ShipCity

Upvotes: 1

Related Questions