SQL Server Group By with Multiplication

Question

I have this SQL Server 2005 Query

SELECT OI.BILL_NO,OI.ITEM_NAME,ODS.QUANTITY
FROM ORDER_ITEMS OI
INNER JOIN ORDER_ASSIGNMENTS OAS ON OI.BILL_NO=OAS.BILL_NO
INNER JOIN ORDERS ODS ON OI.BILL_NO = ODS.BILL_NO
INNER JOIN ORDERS OD ON OD.BILL_NO = OI.BILL_NO
WHERE OAS.ASSIGNMENT_TYPE= 'TO_STICHING' AND OAS.EMPLOYEE_NAME = 'AMIT'

I am getting this result :

Now my requirement is to to get the total Quantity of Every Distinct ITEM_NAME, Example:

CHURIDER           5    
CHURI PA           4
LACHA              1  
ASTRA              1
ASTRA LACHA DOUBLE 1

like this,all Distinct ITEM_NAME and their Total Quantity

Are you getting an error when you try to solve this with GROUP BY? Post your query and results. — Tab Alleman, Jun 08 '17 at 12:56
Possible duplicate of [SQL Server : SUM() of multiple rows including where clauses](https://stackoverflow.com/questions/1607720/sql-server-sum-of-multiple-rows-including-where-clauses) — Tab Alleman, Jun 08 '17 at 12:59

score 2 · Accepted Answer · answered Jun 08 '17 at 12:57

This looks like a simple aggregation query with group by, and sum():

SELECT OI.ITEM_NAME,sum(ODS.QUANTITY) as Quantity
FROM ORDER_ITEMS OI
INNER JOIN ORDER_ASSIGNMENTS OAS ON OI.BILL_NO=OAS.BILL_NO
INNER JOIN ORDERS ODS ON OI.BILL_NO = ODS.BILL_NO
INNER JOIN ORDERS OD ON OD.BILL_NO = OI.BILL_NO
WHERE OAS.ASSIGNMENT_TYPE= 'TO_STICHING' AND OAS.EMPLOYEE_NAME = 'AMIT'
group by OI.ITEM_NAME

score 1 · Answer 2 · answered Jun 08 '17 at 13:00

You can get the sum of a column then group it by the name so

SELECT OI.BILL_NO,OI.ITEM_NAME,SUM(ODS.QUANTITY)
FROM ORDER_ITEMS OI
INNER JOIN ORDER_ASSIGNMENTS OAS ON OI.BILL_NO=OAS.BILL_NO
INNER JOIN ORDERS ODS ON OI.BILL_NO = ODS.BILL_NO
INNER JOIN ORDERS OD ON OD.BILL_NO = OI.BILL_NO
WHERE OAS.ASSIGNMENT_TYPE= 'TO_STICHING' AND OAS.EMPLOYEE_NAME = 'AMIT' 
GROUP BY OI.BILL_NO,OI.ITEM_NAME

Blasanka · Answer 3 · 2017-06-08T13:51:50.623

You need to use aggregate function SUM().

SELECT OI.BILL_NO,OI.ITEM_NAME, SUM(ODS.QUANTITY) AS Qty

AS used to add column name when displaying result because when you use aggregation function column name for that shows as (no column name).

One last thing, you need to add other columns that you are selecting to GROUP BY clause because you are getting sum of each and every quantity so you need to group the other columns. read more and this.

Add after WHERE clause

GROUP BY OI.BILL_NO,OI.ITEM_NAME

SQL Server Group By with Multiplication

3 Answers3