?
Solved

SQL Query - Summarize Field and Remove Columns

Posted on 2009-02-12
8
Medium Priority
?
190 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

0
Comment
Question by:bbanis2k
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23626099
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
 

Author Comment

by:bbanis2k
ID: 23626167
It did not like that...

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

Expert Comment

by:Kyle Abrahams
ID: 23626239
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bbanis2k
ID: 23626267
Hmmm  Didn't like that either.

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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23626290
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23626360

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
 

Author Comment

by:bbanis2k
ID: 23626424
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
 

Author Closing Comment

by:bbanis2k
ID: 31546276
You got it!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question