CODEWITHSUNDEEP
sqlsql-server
don
don

Reputation: 65

Sum of values against each item number?

I have write sql server query which give me this resultlink

Here you can see ITEMNO is repeating ,i want sum of itemno against two columns which are TOTALQUANTITYUSE and TOTALQUANTITYSALE . For example i want one entry against each itemno e.g ITEMNO 220415 only one time in single row and sum of values against this item number will be display on one row.Hope you understand.

SELECT a.ITEMNO,(COUNT(a.ITEMNO)*a.QTY) as 'Total Quantity USE', 
(COUNT(WR.ITEMEMPNO)*WR.QTY) AS 'Total Quantity Sale'
 FROM InvItems a
  inner join WRKTRANS WR ON WR.ITEMEMPNO=a.ITEMNO
  inner join InvHdr HR ON HR.INVTYPE =a.TYPE
  WHERE a.ITEMNO IS NOT NULL AND a.TYPE='3'
  group by a.ITEMNO,WR.QTY,a.QTY

Upvotes: 0

Views: 107

Answers (4)

lokesh
lokesh

Reputation: 85

You need to write multiple CTE statement in order to have extra attribute in your SELECT statement. Few addition in @Abhishek code, please check below.

  ;WITH CTE_A AS 
  (
    SELECT a.ITEMNO,(COUNT(a.ITEMNO)*a.QTY) as 'Total Quantity USE', 
    (COUNT(WR.ITEMEMPNO)*WR.QTY) AS 'Total Quantity Sale'
     FROM InvItems a
      inner join WRKTRANS WR ON WR.ITEMEMPNO=a.ITEMNO
      inner join InvHdr HR ON HR.INVTYPE =a.TYPE
      WHERE a.ITEMNO IS NOT NULL AND a.TYPE='3'
      group by a.ITEMNO,WR.QTY,a.QTY
  ),

  WITH CTE_B AS
  (
    SELECT ITEMNO,SUM([Total Quantity USE]),SUM([Total Quantity Sale])
    FROM CTE_A
    GROUP BY ITEMNO
  )

  SELECT *
  FROM 
  CTE_B as B
  INNER JOIN
  Original_Table_For_MoreColumn as C on B.ItemNo =C.ItemNO

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use aggragate function 

    select itemno,sum([Total Quantity USE] totalQty),
   sum( [Total Quantity Sale] ) as TotalSale,
   count(itemno) as total_iteam from yourtable t    
    group by  itemno

Upvotes: 0

akshay
akshay

Reputation: 777

SELECT ItemNo, SUM([Total Quantity USE]) AS "Total Quantity USE", SUM([Total Quantity Sale]) AS "Total Quantity Sale"
FROM (your_query) AS q
GROUP BY ItemNo

Upvotes: 1

Abhishek
Abhishek

Reputation: 2490

Something like this should work (aggregate the records based on itemno and display the sum) - 

;WITH CTE AS (
    SELECT a.ITEMNO,(COUNT(a.ITEMNO)*a.QTY) as 'Total Quantity USE', 
    (COUNT(WR.ITEMEMPNO)*WR.QTY) AS 'Total Quantity Sale'
     FROM InvItems a
      inner join WRKTRANS WR ON WR.ITEMEMPNO=a.ITEMNO
      inner join InvHdr HR ON HR.INVTYPE =a.TYPE
      WHERE a.ITEMNO IS NOT NULL AND a.TYPE='3'
      group by a.ITEMNO,WR.QTY,a.QTY)
    SELECT ITEMNO,SUM([Total Quantity USE]),SUM([Total Quantity Sale])
    FROM CTE
    GROUP BY ITEMNO

Upvotes: 3

Related Questions