Using group by in mysql

Question

I have the following table creation scripts:

CREATE TABLE assetcost(
assettype VARCHAR(20) PRIMARY KEY,
rentamt INT
);

CREATE TABLE furnishitem(
itemid VARCHAR(4) PRIMARY KEY CHECK(itemid LIKE 'I%'),
description VARCHAR(30),
availablesets INT,
assettype VARCHAR(25),
specialCharge CHAR(1) CHECK(specialcharge IN ('Y','N')),
FOREIGN KEY(assettype) REFERENCES assetcost(assettype)
);

CREATE TABLE custdetail(
custid VARCHAR(5) PRIMARY KEY CHECK(custid LIKE 'C%'),
custname VARCHAR(30)
);


CREATE TABLE transaction(
transid INT UNIQUE,
custid VARCHAR(5) ,
itemid VARCHAR(4),
sets INT,
days INT,
amount INT,
returned char(1) Check (returned in('Y','N')),
FOREIGN KEY(custid)REFERENCES custdetail(custid),
FOREIGN KEY(itemid)REFERENCES furnishitem(itemid) 
);

I am getting error when writing query to display the custid and custname of those customers who has/have paid minimum of total amount, irrespective of the item returned.

My query is:

select t.custid,c.custname
    -> from transaction t inner join custdetail c
    -> on t.custid=c.custid
    -> group by t.custid
    -> having sum(amount)=(select min(sum(amount) from transaction group by custid);

Answer 1

You may be able to solve this in a more elegant fashion, but a quick fix to your problem would be:

SELECT t.custid, c.custname
FROM TRANSACTION t
INNER JOIN custdetail c ON t.custid = c.custid
GROUP BY t.custid
HAVING sum(amount) = (
    SELECT sum(amount)
    FROM TRANSACTION
    GROUP BY custid
    ORDER BY 1 ASC 
    LIMIT 1
    );

You can't really do min(sum(amount)) in a single go. This approach will get you the row with the sum for the custid with minimum value.