asked on

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

Kyle Abrahams, PMP

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

bbanis2k

ASKER

It did not like that...

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.

Kyle Abrahams, PMP

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

bbanis2k

ASKER

Hmmm Didn't like that either.

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

Kyle Abrahams, PMP

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbanis2k

ASKER

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.

bbanis2k

ASKER

You got it!