Solved

I need a sql view to return a total from and existing table

Posted on 2013-11-25
2
258 Views
Last Modified: 2013-11-29
I am looking for a query/view to add to a database to retrieve and combine data from an existing table. Table named "Bin" for the purpose of a crystal report. The table consists of 1000's of items, many duplicates in different bins with a different on hand and burn QTY. The results should not duplicate items, should not list any bin but should add the OnHand and burn QTY of duplicate items as TotalQTY

Item Bin      OnHand      Burn      
abc        1           2                 0      
dgf       2           3                 0      
abc       3           4                 3      
abc       4           8            8      
acdf       5           0                 0      
dgf       6           0                 0      
xyz       7           1               1
                        
                        
                        
Query should combine like items and add their onHand and burn colums to return the below results      
                  
Item         TotalQTY                  
abc            25                  
dgf             3                  
acdf             0
xyz          2
0
Comment
Question by:Mcottuli
2 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 250 total points
ID: 39676693
select Item, SUM(OnHand + Burn) as TotalQTY                  
  from bin
 group by Item

Open in new window

0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39676695
Try something like

Select Item, Sum(OnHand) + Sum(Burn) as TotalQty
From TableBin
Group By Item

Open in new window


mlmcc
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

856 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