We help IT Professionals succeed at work.

SQL Query - Summarize Field and Remove Columns

bbanis2k
bbanis2k asked
on
Medium Priority
202 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
select Inventory.Site as Site,
      Inventory.ItemNumber as Item,
       Sum(Inventory.OnHandQuantity) as Total,
from Inventory
group by
       Inventory.ItemNumber,
order by  Inventory.ItemNumber,

Author

Commented:
It did not like that...

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

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

Author

Commented:
Hmmm  Didn't like that either.

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

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

Commented:
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
Data Engineer
CERTIFIED EXPERT
Commented:

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.

Author

Commented:
You got it!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.