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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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
ochnessAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.