Can you sum one field in a list of fields? i.e.
SELECT job_line_discount.disc_id, job_line_discount.disc_amt, sum(job_actual_cost.progress_bill_amt) as pamount FROM job, job_actual_cost job_line_discount
Something's not quite right with that syntax. It looks like there should be a comma between the last two names in the FROM clause; otherwise,
job_line_discount will be an alias for
job_actual_cost. Based on what's in your SELECT list, those are probably separate tables.
To answer your question, yes, you can sum one field in a list of fields, but then all other fields that are not used in an aggregate function like SUM() must be listed in the GROUP BY clause --
select job_line_discount.disc_id , job_line_discount.disc_amt , sum(job_actual_cost.progress_bill_amt) as pamount from job , job_actual_cost , job_line_discount group by job_line_discount.disc_id , job_line_discount.disc_amt
Presumably, there will also be a WHERE clause, between the FROM and GROUP BY. Otherwise you'll get a cross join, and you probably don't want that.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.