Answered

Get the summation of a column group by another in a View

(0) Share

Report

Posted on by Community Member

I have created the following view based on the joins you can see in the screenshots.

And the result is shown here:

What I want is to sum the Qty based on StatusReceipt. So instead of 4 lines of Registered I want a single line with the Qty of 4

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Martin Dráb 237,855 Most Valuable Professional on at

Like (0)

Report

What's the problem?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The problem is I don't know how to do it ;)

I know how to group the StatusReceipt column but I don't know how to sum the Qty?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,855 Most Valuable Professional on at

Like (2)

Report
Let me focus on InventTrans table only, so we don't get overwhelmed with all the other stuff in your view. If you do many things at once, you'll often achieve very little.

Start with thinking about what SQL code you want your query definition to generate (a view is based on a query). If you want to sum values in a field, you use the aggregation function called SUM. And grouping is done with the GROUP BY clause. Therefore the SQL code would look like this:

select sum(Qty) from InventTrans group by StatusReceipt

To add such as summed field, add Qty to the field list and then change its Aggregation property from None to Sum. You know how to add grouping, so I'm not going to describe it.

If you don't get right results, you can review the actual query in database (after DB synchronization) and review how it differs from the query that you intended to generate.

If you're not familiar with grouping, you must realize that the only fields that have values are those used for grouping or are aggregated. Values of other fields aren't defined and therefore you'll see them as empty.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Martin I wasn't aware of the Aggregation Property in View Fields

Was this reply helpful? Yes No
Suggested answer

ScorpioDax 333 on at

Like (0)

Report

The Aggregation from None to Sum helped me in so many other reports i created, Thanks Martin!

Was this reply helpful? Yes No