SQL Query - Summarize Field and Remove Columns

In the output I only want the Item and Quantity columns.  This is because the other fields would skew the Total columns.

Thx,
B.
select Inventory.Site as Site, 
       Inventory.Location as Location, 
       Inventory.ItemNumber as Item,
       Sum(Inventory.OnHandQuantity) as Total,
       Item.Type as Category, 
       Item.Description as Description
from Inventory inner join Item on Inventory.ItemNumber = Item.ItemNumber 
where (Inventory.Site = 'OMWHSE' OR Inventory.Site = 'SAWHSE') AND (Item.Type = '900' OR Item.Type = '3112') 
group by Inventory.Site, 
       Inventory.Location, 
       Inventory.ItemNumber,
       Item.Type, 
       Item.Description
order by Item.Type;

Open in new window

bbanis2kAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:

Site should be removed from SELECT clause as it is not included in GROUP BY clause. Also you don't want that column.
select ItemNumber as Item,
       Sum(OnHandQuantity) as Total
from Inventory 
group by 
       ItemNumber
order by  ItemNumber

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select Inventory.Site as Site,
      Inventory.ItemNumber as Item,
       Sum(Inventory.OnHandQuantity) as Total,
from Inventory
group by
       Inventory.ItemNumber,
order by  Inventory.ItemNumber,
0
 
bbanis2kAuthor Commented:
It did not like that...

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Kyle AbrahamsSenior .Net DeveloperCommented:
sorry, comma there.

select Inventory.Site as Site,
      Inventory.ItemNumber as Item,
       Sum(Inventory.OnHandQuantity) as Total
from Inventory
group by
       Inventory.ItemNumber,
order by  Inventory.ItemNumber
0
 
bbanis2kAuthor Commented:
Hmmm  Didn't like that either.

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'order'.

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
last comma:

select Inventory.Site as Site,
      Inventory.ItemNumber as Item,
       Sum(Inventory.OnHandQuantity) as Total
from Inventory
group by
       Inventory.ItemNumber
order by  Inventory.ItemNumber
0
 
bbanis2kAuthor Commented:
Msg 8120, Level 16, State 1, Line 1

Column 'Inventory.Site' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0
 
bbanis2kAuthor Commented:
You got it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.