member function to get the sum in oracle

Question

I have a type called sell_type defined as

CREATE OR REPLACE TYPE sell_type AS OBJECT (
    dname VARCHAR (50),
    car_model VARCHAR(20),
    make VARCHAR (20),
    price NUMBER (10,2),
    MEMBER FUNCTION total_sales RETURN NUMBER
);
/

Body:

CREATE OR REPLACE TYPE BODY sell_type AS
    MEMBER FUNCTION total_sales RETURN NUMBER IS
    BEGIN
      RETURN SELF.price;
    END total_sales;
END;
/

And an object table

CREATE TABLE sell of Sell_Type;
/

I want to get the total sales for a given seller with something like:

select s.total_sales() from sell s
where s.dname = 'John Doe';

But what I get is a separate list of prices of all the sales of that given seller, rather than the total of those prices.

I know that I have to fix my type body somehow. I tried to use the SUM() inside the return but that didn't work. Can someone please help?

Answer 1

This body will do the trick. But it will return the same value for each row on the sell table. Therefore, you have to use 'group by' or 'max()' if you want to see just one row of result.

CREATE OR REPLACE
TYPE BODY SELL_TYPE AS
MEMBER FUNCTION total_sales (p_seller varchar2) RETURN NUMBER IS
total_price NUMBER;
BEGIN
SELECT sum(s.price) INTO total_price FROM sell s where s.dname = p_seller;
RETURN total_price;
END total_sales;
END;
/

select query will look like this.

select s.total_sales('John') 
from sell s GROUP BY s.total_sales('John');