Home > Software design >  How to select best customers and group them by how much they paid
How to select best customers and group them by how much they paid

Time:06-05

I am using SQL server to select this table and group the customer id and first name according to their total payments made to the store. I tried using

select custid, name, sum(payment)
from transactions
group by custid
order by payment desc

but it returns the following error:

not contained in either an aggregate function or the GROUP BY clause

Could you please clarify what I did wrong?

this is my customer table

cust id payment name
T001 60 katy
T002 12 amy
T003 40 leon
T001 20 katy

this is the result table I want to get

cust id payment name
T001 80 katy
T003 12 amy
T002 40 leon

CodePudding user response:

There's a space in your cust id column. Your code does not contain the space. Try it with a space between cust and id.

CodePudding user response:

The error refers to the name or payment columns, both of which you refer to without aggregating.

You need to order by the same expression or provide the column an alias; column name inconsistencies aside, try:

select Cust_Id, sum(Payment) as TotalPayment, Name
from transactions
group by Cust_Id, Name
order by TotalPayment desc;
  • Related