Totals in Query

[Former Member]

Hi Everybody,

Question: Is it possible to have total of a specific column when I create a query?

In my example I created a query for all the costs related to a project (tables OPOR and POR1). I have the column of the price of every lineitem (Price from table POR1). Query works but I would need to have the total of the prices, possibly in the field called QI Direct Access Mtx1 (the one in the grid of the results). Is it possible?

Thanks in advance

[former_member583013 · Accepted solution]

Federico,

In SQL you have a function called Rollup which can be used to show totals.

Please refer to SQL Server help files for syntax

Suda

[Former Member · Accepted solution]

Thanks very much everybody

Rgds

[former_member583013]

Federico,

That option is not available as default. It has to be User initiated.

Suda

[Former Member]

Suda,

Thanks for your answer;

what do you mean with "not available as default"?

Do you mean that there isn't a specific check in a menu (but maybe I can enable it, query by query, with a script or something) or that is impossible to create a query with totals automatically shown?

Sorry if I bother you but, believe me, it's more difficult to explain to users to click CTRL + Left button than create every query with totals shown...

Thanks in advance and sorry again

[former_member204969]

You can make totals in sql by using the group by close, but it may be inconvenient with detailed lists. You can also use something like this :

SELECT top 100

T0.ItemCode, T0.OnHand, T0.IsCommited, T0.AvgPrice, T0.Locked

FROM OITW T0

Union all

Select null,

(select sum(w.onhand) from

(

SELECT top 100

T0.ItemCode, T0.OnHand, T0.IsCommited, T0.AvgPrice, T0.Locked

FROM OITW T0

) w)

,null,null,null

[Former Member]

Hi

Don't know whether it's available by default. I found it by accident one day.

Regards

Daan

[Former Member]

Hi Federico

You can view the total of a column in the query result by holding the CTRL key and clicking on the label of the column.

Regards

Daan

[Former Member]

So simple??? That's greate! Thank you very much.

Is it possible to have it by default? I'm sure users forget this trick...

Thanks a lot in advance