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);

In an RDBMs, every table should, in principle, have a PRIMARY KEY. — Strawberry
– Strawberry, Commented Nov 25, 2013 at 14:11

Filipe Silva · Accepted Answer · 2013-11-25 14:08:41Z

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.

answered Nov 25, 2013 at 14:08

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3029086 Over a year ago

Thanks Filipe. The above solution works well for me. Could you please explain why the following query gives ERROR 1111 (HY000): Invalid use of group function:select custid,min(sum(amount)) from transaction group by custid; In oracle it is working fine but giving error in MySql.

Filipe Silva Over a year ago

It's the MIN(SUM(abount)). You can't do both MIN and SUM at the same time. You would have to do first the sum and group by custid, and then find the MIN from those in an outside query.

Collectives™ on Stack Overflow

Using group by in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related