SQL - Find AVG spent of a customer by how much a client spent o-CodePudding

I have a table of id client, date, services, money_spent Clients in a single date can have more raws because they bought more services.

I have to create a table where I see the average spent for the clients who come once in the shop, twice, and so on.

This is the table I created

I would like to group by the people who has count value 1, 2 , 3 and so on to see how much they spent .

At the moment I'm using this query:

SELECT ID_Client, SUM(Money_spent),COUNT(DISTINCT Date) FROM Services_Data sd GROUP BY ID_Client

CodePudding user response：

You just need to aggregate twice.

By placing your query inside brackets (a sub-query) you can then treat it as a table (data set) to run another query on.

SELECT
  date_count, 
  SUM(spent) AS spent 
FROM
(
  SELECT ID_Client, SUM(Money_spent) AS spent, COUNT(DISTINCT Date) AS date_count
  FROM Services_Data
  GROUP BY ID_Client
)
  AS client_summary
GROUP BY
  date_count

CodePudding user response：

You can use the output of your query as the input for another:

select times, avg(spent) from (
  select ID_Client, sum(Money_spent) as spent, count(distinct Date) as times
  from Services_data
  group by ID_Client
)
as q1
group by times