Custom Query (Get Average)

Wappler General Need Help
1

I have a table with Client IDs and amount spent. I can get a unique count of the clients and I can get a total spent. How do I get the average spent over all?

So I need to do my Total divided by Clients to get the average but not sure how to do that in MySQL. Been trying everything.

Screen Shot 2020-02-06 at 11.57.19 AM
Screen Shot 2020-02-06 at 11.57.19 AM696×406 55.1 KB 

SELECT
	COUNT(DISTINCT cost_clientid) AS Clients, 
	SUM(cost_amount) AS Total
FROM
	p_skillshighway_costs
2

Will Average work?

https://www.w3schools.com/sql/func_mysql_avg.asp

1 Like
3

We have AVERAGE available in the Aggregate functions in the query builder as well.

4

Yes, I know there is Average there but that will only get me the average of a column. For example it would get me an average of costs per transaction. but I need to know the averages based on the number of distinct clients.

5

Did you try this?

SELECT
	COUNT(DISTINCT cost_clientid) AS Clients, 
	SUM(cost_amount) AS Total,
    (SUM(cost_amount) / COUNT(DISTINCT cost_clientid)) as Average
FROM
	p_skillshighway_costs
2 Likes
6

You sir, are a rock star! That's about the only combination I didn't try. Works perfectly!

Once again I owe you some :beers:

Thank you!

2 Likes