How to sum multiple line items in SQL

I am working on creating a query in SQL to use on a report. I'm having trouble summing some values, so here is my question:

I have a table that shows how many inventory items were sold by period, by warehouse site, and the for each fiscal year. I have 5 warehouse sites, so each inventory item will have 5 rows of data (one for each warehouse site), and 12 columns (for each period (month)). Then each inventory item may have been sold during a fiscal year, so some items may have 2, 3, 4, or more years of data.

What I'm trying to do is sum each of the inventory items by warehouse site by fiscal year. So instead of each inventory item having 5 rows for each year, there will be one summed row for each year.

I hope I have explained what I'm trying to do well enough. Please let me know if I can help explain what I'm trying to do any better.

Thanks.
LVL 1
ochnessAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ochnessConnect With a Mentor Author Commented:
Thank you guys for your suggestions. I finally pulled my head out and figued out how to do this. I have attached the solution I was able to come up with.

Thank you all again for your reply's and suggestions.
select 
Item2Hist.FiscYr, 
Item2Hist.InvtID,
sum(Item2Hist.PTDQtySls00), 
sum(Item2Hist.PTDQtySls01), 
sum(Item2Hist.PTDQtySls02), 
sum(Item2Hist.PTDQtySls02), 
sum(Item2Hist.PTDQtySls03), 
sum(Item2Hist.PTDQtySls04), 
sum(Item2Hist.PTDQtySls05), 
sum(Item2Hist.PTDQtySls06), 
sum(Item2Hist.PTDQtySls07), 
sum(Item2Hist.PTDQtySls08), 
sum(Item2Hist.PTDQtySls09), 
sum(Item2Hist.PTDQtySls10), 
sum(Item2Hist.PTDQtySls11), 
sum(Item2Hist.YTDQtySls)
from item2hist
group by Item2Hist.InvtID, Item2Hist.FiscYr
order by Item2Hist.InvtID, Item2Hist.FiscYr desc

Open in new window

0
 
Alfred A.Commented:
Hi,

I am not sure how many tables you have but you can try the following:


SELECT Item, WarehouseSite, FiscalYear, SUM(Jan) AS 'January', SUM(Feb) AS 'February', SUM(Mar) AS 'March', .... and so on up to december
FROM Inventory
GROUP BY Item, WarehouseSite, FiscalYear

Please explained further if I missed anything.

I hope this helps.
0
 
Rajkumar GsSoftware EngineerCommented:
Do you want to SUM all the values in the 12 columns for each month ? And show group by  inventory items & fiscal year ?

Raj

0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LowfatspreadCommented:
Select Itemid,warehouseid
           ,sum(case when theyear = y1 then sold else 0 end) as current
 ,sum(case when theyear = y2 then sold else 0 end) as Yr_1
 ,sum(case when theyear = y3 then sold else 0 end) as Yr_2
 ,Sum(case when theyear = y4 then sold else 0 end) as Yr_3
 ,Sum(case when theyear = y5 then sold else 0 end) as Yr_4
from
(Select itemid, coalesce(mth1,0)+coalesce(mth2,0)+....coalesce(mth12,0) as sold
          ,theyear,warehouseid
 from yourtable
) as x
, (select yr as y1, yr - 1 as y2, yr - 2 as y3 , yr - 3 as y4 , yr -4 as y5
      from (select  year(getdate()) as yr) as y
  ) as z
group by itemid,warehouseid
order by 1,2  
0
 
Rajkumar GsSoftware EngineerCommented:
If NO, then

SELECT [Year], InvItem, SUM(Jan) AS 'Jan', SUM(Feb) AS 'Feb', SUM(Mar) AS 'Mar',
....  
,SUM(Dec) AS 'Dec'
FROM Inventory
GROUP BY  [Year], InvItem

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
If YES, then

SELECT [Year], InvItem, (SUM(Jan) + SUM(Feb) + SUM(Mar)
....  
+ (SUM(Dec)) AS MonthValues
FROM Inventory
GROUP BY  [Year], InvItem

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Can you please paste query in main comment area?

Since I am from mobile, can't read code-snipplet fully

Raj
0
 
ochnessAuthor Commented:
RajkumarGS here you go:

select
Item2Hist.FiscYr,
Item2Hist.InvtID,
sum(Item2Hist.PTDQtySls00),
sum(Item2Hist.PTDQtySls01),
sum(Item2Hist.PTDQtySls02),
sum(Item2Hist.PTDQtySls02),
sum(Item2Hist.PTDQtySls03),
sum(Item2Hist.PTDQtySls04),
sum(Item2Hist.PTDQtySls05),
sum(Item2Hist.PTDQtySls06),
sum(Item2Hist.PTDQtySls07),
sum(Item2Hist.PTDQtySls08),
sum(Item2Hist.PTDQtySls09),
sum(Item2Hist.PTDQtySls10),
sum(Item2Hist.PTDQtySls11),
sum(Item2Hist.YTDQtySls)
from item2hist
group by Item2Hist.InvtID, Item2Hist.FiscYr
order by Item2Hist.InvtID, Item2Hist.FiscYr desc
0
 
Rajkumar GsSoftware EngineerCommented:
Is it same as I suggested (Which starts with "If NO...") ?

Raj?

Raj
0
All Courses

From novice to tech pro — start learning today.