Solved

SUM Statement

Posted on 2008-10-31
2
232 Views
Last Modified: 2010-04-21
Below is my select statement. I need to sum the three EST* columns, the the same for the thee ACT* columns, then create the difference. I must be missing something, I keep getting errors.

Help Please.


SELECT     BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE, EST_MATERIAL_COST, EST_LABOR_COST, EST_SERVICE_COST, ACT_MATERIAL_COST,
                      ACT_LABOR_COST, ACT_SERVICE_COST
FROM         WORK_ORDER
0
Comment
Question by:jethrow
2 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 125 total points
ID: 22853897
If you want to produce these calculations for each row then you can try the code below.

SELECT     BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE, (EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST) AS ESTIMATED_COST, (ACT_MATERIAL_COST +
                      ACT_LABOR_COST + ACT_SERVICE_COST) AS ACTUAL_COST, (EST_MATERIAL_COST + EST_LABOR_COST + EST_SERVICE_COST) - (ACT_MATERIAL_COST +
                      ACT_LABOR_COST + ACT_SERVICE_COST) AS DIFFERENCE  
FROM         WORK_ORDER

but if you want to produce total cost for two or more rows then you must use aggrate functions

SELECT     BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE, (sum(EST_MATERIAL_COST) + sum(EST_LABOR_COST) + sum(EST_SERVICE_COST)) AS ESTIMATED_COST, (sum(ACT_MATERIAL_COST) + sum(ACT_LABOR_COST) + sum(ACT_SERVICE_COST)) AS ACTUAL_COST, (sum(EST_MATERIAL_COST) + sum(EST_LABOR_COST) + sum(EST_SERVICE_COST)) - (sum((ACT_MATERIAL_COST) +
                      sum(ACT_LABOR_COST) + sum(ACT_SERVICE_COST)) AS DIFFERENCE  
FROM         WORK_ORDER
WHERE some condition
GROUP BY  BASE_ID, STATUS, PRODUCT_CODE, SCHED_START_DATE
0
 

Author Closing Comment

by:jethrow
ID: 31512199
Thanks for the quick responce! Perfect!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now