Sql need to sum column several times and break result in one row horizontally

debugcn 投稿 Dev

Evdo1

SQL needed to sum column several times and break result in one row by store horizontally, but I'm getting null rows mixed with totals need to take them out
Query:

 SELECT distinct IdPurch, 
  Datediff(week, (dateadd(day,-1, '2021-01-03')), '2021-01-09') AS wks,
  (Select sum(WgtSold)   where   store = '000001'  ) as lam,
  (Select sum(WgtSold)   where   store = '000002') as Riv  
  FROM [LAHAMARK].[dbo].[itemSale]
  LEFT OUTER Join [BRdata].[dbo].[Item]  on [LAHAMARK].[dbo].[itemSale].UPC15 = [BRdata].[dbo].[Item].[UPC]  
  LEFT OUTER join [BRdata].[dbo].[ItemMovement] on [LAHAMARK].[dbo].[itemSale].UPC15 = [BRdata].[dbo].[ItemMovement].[UPC]    
  where  Date  between   '2021-01-03' and  '2021-01-03'  and   idPurch = '1018'
  group by IdPurch,Store

query Result :

Gordon Linoff

You seem to want conditional aggregation:

select IdPurch, 
       sum(case when store = '000001' then WgtSold end) as lam,
       sum(case when store = '000002' then WgtSold end) as Riv  
from [LAHAMARK].[dbo].[itemSale] its join
     [BRdata].[dbo].[Item] i
     on its.UPC15 = i.[UPC] join
     [BRdata].[dbo].[ItemMovement] im
     on its.UPC15 = im.[UPC]    
where Date  between '2021-01-03' and '2021-01-03'  and
      idPurch = 1018
group by IdPurch ;

Notes:

Table aliases make the query much easier to write and to read.
The week calculation is superfluous for only one week and has nothing really to do with the gist of the question, so I removed it.
There is no need to aggregate by store. You don't want a separate row for each store.
I removed the single quotes around 1018. It looks like a number and ids typically are numbers. If it is a string, definitely include the single quotes, though.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]