Skip to main content

Notifications

Announcements

No record found.

Finance | Project Operations, Human Resources, ...
Answered

Sum function with assetID from AssetTrans table

(0) ShareShare
ReportReport
Posted on by

Hi All,

I need to write select statement for summing the AmountMST value based on AssetID, 

Data is like from AssetTrans Table:

AssetId

AmountMST

AS001

10

AS001

20

AS002

30

AS002

20

Result should be :

AS001 – 30    // sum of AS001 (10+20)

AS002 – 50    // sum of AS002 (30+20)

I am able to get the sum value based on asset Id but in the result only sum value is displaying not the asset id.

select statement is:

select sum(AmountMST),AssetId from assetTrans
where assetTrans.AssetId == "AS001";

info(strFmt('%1,%2',assetTrans.AmountMST,assetTrans.AssetId));

Output is 30, (not displaying the assetid)

Please advice how can i get the sum value and assetid number in the output.

Regards,

Akbar

  • Martin Dráb Profile Picture
    Martin Dráb 230,966 Most Valuable Professional on at
    RE: Sum function with assetID from AssetTrans table

    The easiest thing, and the best for learning, is trying it. :-)

    Then you can question your assumption that strings can't be sorted.

  • waytod365 Profile Picture
    waytod365 351 on at
    RE: Sum function with assetID from AssetTrans table - D365FO

    Hi Martin sir,

    AssetId is string it's won't return maxOf value am i right? Correct me if I'm wrong.

    Thanks 

  • Akbarpasha Profile Picture
    Akbarpasha on at
    RE: Sum function with assetID from AssetTrans table - D365FO

    Thank you very much Martin , i am able to get the desired output now.

    Sure i will use rich formatting view from next time onwards.

    Thanks again, it helped me a lot.

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,966 Most Valuable Professional on at
    RE: Sum function with assetID from AssetTrans table - D365FO

    You neither aggregate nor group by AssetId, therefore its doesn't have any value defined.

    This is an example with groupping:

    select sum(AmountMST), AssetId from assetTrans
    	group by AssetId
    	where assetTrans.AssetId == "AS001";

    And this one with an aggregation:

    select sum(AmountMST), maxOf(AssetId) from assetTrans
    	where assetTrans.AssetId == "AS001";

    By the way, please use Insert > Code (in the rich formatting view) to paste source code.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,187 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,966 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans