MYSQL Multiple queries or single query - which is more efficient

Question

I have a table with 3 columns: Animal, key, owner. Example

Cat     1     Bob
Bird    2     Bob
dog     3     Bob
dog     4     Andy
Lizard  5     Andy
Bird    6     Andy
Cat     7     Andy

and one related table per animal (columns key, weight). For example, table CAT_WEIGHT:

1   12
7   17

I want to find the Min, Max, Average, Total, and Count for each animal type, but only for a particular owner.

Is this possible to calculate these using a single MYSQL query? I know I can do it in multiple queries but am looking for the best way.

Thanks

Please note: One or more of the answers below may no longer apply to this question since it has been edited to be quite different from the original question. — mechanical_meat
– mechanical_meat, Commented Feb 15, 2013 at 0:06

mechanical_meat · Accepted Answer · 2013-02-14 23:21:14Z

3

Yes it is possible to do this using just one query.

All other things being equal you want to use as few queries as possible. Roundtrips to the database are generally some of the more expensive things you'll encounter in programs.

select 
  animal, 
  min(weight)   min_weight, 
  max(weight)   max_weight, 
  avg(weight)   avg_weight, 
  sum(weight)   tot_weight, 
  count(weight) cnt_weight
from 
  your_table
group by 
  animal 
order by animal;

answered Feb 14, 2013 at 23:21

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

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

2 Comments

TSG Over a year ago

I think this is close. I realized the weight is in another table (see revised questoin). Is it still possible in a single query?

mechanical_meat Over a year ago

Revising a question such that it is significantly different from the original question is not necessarily something that is done very often. You may wish to open a separate question. Or maybe someone will come along and answer the question as it now stands.

ethrbunny · Accepted Answer · 2013-02-15 00:20:08Z

0

How about:

select min(Weight), max(Weight), sum(Weight), count(*) from animals_table group by animal

edited Feb 15, 2013 at 0:20

answered Feb 14, 2013 at 23:21

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

1 Comment

mechanical_meat Over a year ago

This would count rows with a NULL weight.

Collectives™ on Stack Overflow

MYSQL Multiple queries or single query - which is more efficient

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related