Count number and multiply with field value (1 Viewer)

[FoolzRailer]

Hello

I'm trying to do a Total query, that is calculating the cost of building certain systems. Right now I'm just trying to count the number of Nodes and multiply that with field UnitPrice, to find out how must the cost of nodes is based on their size (the sum of all the money spent on 1 meter nodes fx.).

However I keep getting an aggregate function error.

Tried to translate my query language. Don't mind the locale settings for ";" or ",":
Format(Sum([Nodes_UP].[UnitPrice]*Count([NodesList].[Nodes]));"Currency")

What am I doing wrong here? Full Code below.

SELECT Nodes_UP.ID, Nodes_UP.Description, Nodes_UP.UnitPrice, "Stk." AS UnitType, Count(NodesList.Nodes) AS Quantities, Format(Sum([Nodes_UP].[UnitPrice]*Count([NodesList].[Nodes])),"Currency") AS Total

FROM List_Nodes INNER JOIN Nodes_UP ON List_Nodes.JoinNodes = Nodes_UP.Join

GROUP BY Nodes_UP.ID, Nodes_UP.Description, Nodes_UP.UnitPrice, "Stk."

ORDER BY Nodes_UP.ID;

 

[June7]

I think you will have to do a separate query that counts the nodes and JOIN that query to Nodes_UP. You could do it as a nested subquery.

Might be a way to day this in report with Sorting & Grouping and aggregate calcs.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Eugene-LS]

What am I doing wrong here?

try replacing the line:

... , Format(Sum([Nodes_UP].[UnitPrice]*Count([NodesList].[Nodes])),"Currency") AS Total ...

to

... , Format(Count([UnitPrice])*Sum([Nodes]),"Currency") AS Total ...

You have a round bracket in the wrong place.

 

[ebs17]

I have shortened the content of the line in question (format, superfluous brackets):

SUM(Nodes_UP.UnitPrice * COUNT(NodesList.Nodes)) AS Total

It looks like you wanted to do two consecutive aggregations in one step:
Count, multiply number by price, sum the whole.
This is not possible. As @June7 says, such steps must take place in their own query levels.

 

[Pat Hartman]

formatting a field this way turns it into a string. do NOT format data you want to do arithmetic with in queries. Format in the report or form using the format property and NOT the format function.