# 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
###### 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.

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
Software 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
Commented:
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
Software 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
Software 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
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.

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

Experts Exchange Solution brought to you by

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

Software EngineerCommented:
Can you please paste query in main comment area?

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

Raj
0
Author 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
Software 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.