Write a Function that takes the customer number as input and returns the purchase

If the total purchase amount for the customer is <25000 , status= Silver, If the total purchase amount for the customer is between 25000 and 50000, status= Gold, If the total purchase amount for the customer is >50000 , status= Platinum. The Payment table:

Cust_number  Check_No   Payment_Date    Amount
103          HQ336336   2004-10-19      6066.78
103          JM555205   2003-06-05      14571.44
103          OM314933   2004-12-18      1676.14
112          BO864823   2004-12-17      14191.12
112          HQ55022    2003-06-06      32641.98
112          ND748579   2004-08-20      33347.88
114          GG31455    2003-05-20      45864.03
114          MA765515   2004-12-15      82261.22
114          NP603840   2003-05-31      7565.08

This is what I've tried:

create function 'pur_stat'(cid int)
returns varchar(20)
begin
declare stat varchar(20);
case
select when amount<25000 then stat='silver'
when amount>=25000 and amount<=50000 then stat='gold'
else 'platinum'
end as stat
return stat

But I am getting error in the query, please help

CodePudding user response：

There are quite a few syntax errors in your code. Try this:

CREATE FUNCTION pur_stat(
cid int
) 
RETURNS VARCHAR(20)
BEGIN
    DECLARE stat VARCHAR(20);
    DECLARE credit numeric;
    SET credit = (select sum(Amount) from Payment where Cust_number = cid);

    IF credit > 50000 THEN
        SET stat = 'platinum';
    ELSEIF (credit >= 25000 AND 
            credit <= 50000) THEN
        SET stat = 'gold';
    ELSEIF credit < 25000 THEN
        SET stat = 'silver';
    END IF;
    -- return the customer level
    RETURN (stat);
END

Fiddle

You can use this function to find the status for example customer 103:

select 103 as Cust_number, pur_stat(103) as purchase_status;

CodePudding user response：

select 
 case
    when amount<25000 then stat='silver'
    when amount>=25000 and amount<=50000 then stat='gold'
    else stat='platinum'
 end
from your_table
where cust_number=

I am not a MySQL's master, but guess, your SELECT-query should like something like this