can't print the names in the right order and the sum of profit is wrong as well

Question

CREATE TABLE seller_details
(
    seller_id integer PRIMARY KEY, 
    seller_name text
);

CREATE TABLE sales_data
(
    seller_id integer, 
    quantity integer, 
    price_per_item integer
);

INSERT INTO seller_details VALUES (1, 'sam');
INSERT INTO seller_details VALUES (2, 'jack');
INSERT INTO seller_details VALUES (3, 'john');

INSERT INTO sales_data VALUES (1, 4, 100);
INSERT INTO sales_data VALUES (1, 5, 200);
INSERT INTO sales_data VALUES (1, 4, 300);
INSERT INTO sales_data VALUES (2, 2, 500);
INSERT INTO sales_data VALUES (2, 9, 300);
INSERT INTO sales_data VALUES (3, 1, 500);

COMMIT;


SELECT * FROM seller_details;
SELECT * FROM sales_data;

SELECT
    seller_name, 
    (quantity * price_per_item) AS total_sales 
FROM
    seller_details sd, sales_data sda
GROUP BY
    seller_name
ORDER BY
    total_sales DESC

Answer 1

You seem to be missing the sum from (quantity*price_per_item) as total_sales in your aggregation, however your real issue is how you have done your join on the tables. Your query is creating a cross join (see below) instead of an inner join i.e. you are generation more records and not based on a matched seller id. I recommend specifying the type of joins in the future

The query below makes these adjustments

select seller_name, SUM(quantity*price_per_item) as total_sales 
from seller_details sd
inner join sales_data sda ON sd.seller_id = sda.seller_id
group by seller_name
order by total_sales desc

seller_name	total_sales
jack	3700
sam	2600
john	500

---

View Demo DB Fiddle

I've included a demo of the cross join results received below for your perusal. When summed, these would give you greater total_sales than desired.

select seller_name, quantity*price_per_item as total_sales 
from seller_details sd,sales_data sda 
order by seller_name

seller_name	total_sales
jack	400
jack	1000
jack	1200
jack	1000
jack	2700
jack	500
john	400
john	1000
john	1200
john	1000
john	2700
john	500
sam	400
sam	1000
sam	1200
sam	1000
sam	2700
sam	500

---

View on DB Fiddle

Let me know if this works for you.