CODEWITHSUNDEEP
mysqlstring
Goofy
Goofy

Reputation: 115

Return string of minimum length in MySQL database

I have a table called "Inventory" with the fields Item (varchar), Amount (int), Type (varchar)

What I would like to extract is rows with the following fields:

Shortest entry in the Item field of all Items of type Type

Sum of all Amounts of all Items of type Type

I have the following:

SELECT Item, sum( Amount ) FROM Inventory GROUP BY Type

which gives what I want except it doesn't return the shortest Item, instead it has one of the other Items (the last one it finds I think). By shortest I mean minimum string length.

Thanks for any help.

Upvotes: 7

Views: 17739

Answers (3)

Girish Rao
Girish Rao

Reputation: 2669

Not sure where this should go, so I'm creating a new answer:

select e.type, min_item.s, e.item from inventory e 
  inner join 
    (select e2.type, sum(e2.amount) as s, e2.item, min(length(e2.item)) as bb 
     from inventory e2 group by e2.type) min_item 
  on (length(e.item)=min_item.bb) 
group by e.type;

I tested this and it worked.

Upvotes: 1

Gaurav
Gaurav

Reputation: 28775

You can get it by sub query.

select type, sum(amount), item 
from inventory 
group by type
having length(item) <= (select min(length(item)) from inventory)

User Order By columnName ASC /DESC for sorting and LIMIT 1 for getting one out of that

Upvotes: 9

Girish Rao
Girish Rao

Reputation: 2669

select type, sum(amount), min(length(item)) from inventory group by type

should do what you want

Upvotes: 1

Related Questions