Solved

SUM Statement

Posted on 2008-10-31
2
233 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

9 Experts available now in Live!

Get 1:1 Help Now