How to use SQL caculation combine two row data in one row ?

Toutes les réponses

• vendredi 22 juin 2012 10:25

   

   
  

  1

  Something like:

  SELECT SUM(inv_qty) AS Total FROM tbl 
  WHERE inv_id = 'PW.K3002.002'
  AND inv_location IN ('Facility', 'MainRoller')

  or,

  SELECT DISTINCT INV_ID, STUFF((select ', ' + REPLACE(inv_location, '', '') 
  FROM tbl FOR XML PATH('')), 1, 2, '') AS Inv_location,
  SUM(inv_qty) AS Inv_Total 
  FROM TBL
  WHERE inv_id = 'PW.K3002.002'
  GROUP BY INV_ID

  
  I'm sure there are better solutions (I'm no developer)

  ---

  
  Thanks,
  
  Andrew Bainbridge
  SQL Server DBA
  
  Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you
  

  • Réponse
  • Citation

   
• vendredi 22 juin 2012 12:05

   

   
  

  0

  Try this,

  SELECT inv_id , SUM(inv_qty) AS Total FROM tbl 
  Group by inv_id 

  
  

  Regards
  Satheesh

  • Réponse
  • Citation

   
• lundi 25 juin 2012 00:57

   

   
  

  0

  Try below.

  declare @t table(inv_id varchar(20),inv_location varchar(20),inv_bin int,inv_qty int)
  insert into @t values('PPE-BLT.001','FC',NULL,150)
  insert into @t values('PW.K3002.002','Facility',NULL,399)
  insert into @t values('PW.K3002.002','MainRoller',NULL,399)
  insert into @t values('C5S-CLR-002','Engineering',NULL,1)
  insert into @t values('PW.K3002.001','Bonding/SRS',NULL,989)
  insert into @t values('PW.K3001.002','Store',NULL,1902)
  insert into @t values('PW.K3002.001','Material',NULL,198)
  select distinct t.inv_id,
  stuff((select '+' + inv_location 
  from @t t1 where t.inv_id = t1.inv_id  for xml path('')),1,1,'') as inv_location
  ,sum(t.inv_qty) over (partition by t.inv_id) as inv_qty
  from @t t
  RESULTS
  --------
  inv_id	   inv_location	                  inv_qty
  C5S-CLR-002	Engineering	         1
  PPE-BLT.001	FC	                  150
  PW.K3001.002	Store	                  1902
  PW.K3002.001	Bonding/SRS+Material	1187
  PW.K3002.002	Facility+MainRoller	798

  
  

  • Modifié Eshani Rao lundi 25 juin 2012 00:57
  • Modifié Eshani Rao lundi 25 juin 2012 00:58
  • Modifié Eshani Rao lundi 25 juin 2012 01:00
  •  
  • Réponse
  • Citation

   
• lundi 25 juin 2012 01:33

   

   
  

  0

  Hi,as below is my SQL script, i had join both table, please advise

  Select distinct A.INV_ID,A.INV_TYPE,A.INV_SHORTDESC,A.INV_ROL ,sum(B.INV_QTY) as TOTALQTY,
  Case When A.INV_USAGE = 0
                  Then 0            
             else CAST((b.INV_QTY) / a.INV_USAGE AS int)
                            
                  End As DSB
  
  from OTH_INV_DETAILS A join OTH_INV_QTY_LOC B  ON A.INV_ID= B.INV_ID
  where 1=1
  or
  B.INV_QTY <= A.INV_ROL
  group by A.INV_ID,A.INV_TYPE,A.INV_SHORTDESC,A.INV_ROL,B.INV_QTY,A.INV_USAGE
  
  

  
  

  • Réponse
  • Citation