Multiple functions in SQL select statement

Tina K
Tina K used Ask the Experts™
on
For some reason, this really has me stumped.  I'm trying to retrieve the max transaction id from the inventory_transaction table, but also need to sum up some costs.  As soon as I start adding any of the costs, no matter how I do it, the max(inventory_trans) fails and I get a record for each different cost.

Example - this works:

this one works
This doesn't:  I get 151 records, rather than the max:

not
Also doesn't work:

also not
Any help appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT part_id, MAX(...), SUM(ACT_BURDEN_COST + ...) AS act_cost
FROM ...
WHERE ...
GROUP BY part_id
Tina KSystems Specialist

Author

Commented:
Hi Scott -
Gave that a try and get the same result of multiple records:

not-working-III.JPG
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
No, you kept GROUPing by all the columns.  GROUP only on part_id (see the last query I posted).  If you need workorder_base_id too, then you will have multiple rows by definition because you have multiple workorders.
Tina KSystems Specialist

Author

Commented:
That did the trick Scott!  Thanks so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial