• Hi Guest: Welcome to TRIBE, the online home of TRIBE MAGAZINE. If you'd like to post here, or reply to existing posts on TRIBE, you first have to register. Join us!

SQL Gurus - is there a better way to write this query.

kaniz

TRIBE Member
Ok, so I have a SQL assignment, and one of the questions is

[FONT=&quot]- List the name of the species with the highest total estimated numbers in all sanctuaries combined

The table structure is :
speciess, sanctuary_namess, estimated_numberss
Sample data is like

Monkey, Monkey Sanctuary, 10
Monkey, Monkey Sanctuary2, 10
Bear, Bear Sanctuary, 10

since the sum for monkey would be 20, and the sum for bear is 10, I'd want to list Monkey, 20.

The query I came up with, which seems like the most contrived piece of shit is below, and just wondering if there is a more elegant solution to this?

The obvious one IMO, would of of been along the lines
SELECT MAX(SUM(estimated_numberss)),Speciess from tablename .. but no, that wont work.

[/FONT]select max(est),speciess from
(
select sum(estimated_numberss) est, speciess
from especies_sanctuary
group by speciess
) sub
group by speciess
having max(est) =
(
select max(est) from
(
select sum(estimated_numberss) est, speciess
from especies_sanctuary
group by speciess
) sub2
)
 
Cannabis Seed Wedding Bands

Liquidity

TRIBE Member
select speciess, sum(estimated_numberss) from especies_sanctuary group by speciess where speciess = 'monkey'
 
Last edited:

kaniz

TRIBE Member
Well, that was just sample data - its not /always/ going to be monkey, just the data I have entered in the database, I know it would be that for now.

The table actually has about 100 records in it. There are 5 different sanctuaries, and about 13 different species.

Some sanctuaries have every species in them, some sanctuaries only have a few species in them.


I cant just do a where clause on the monkey - as who knows, if I change the data in the table, the monkey wont be the one with the most estimated numbers.

the query I wrote works - but just strikes me as being a sloppy way of doing it.

but. for example, if the data was

[FONT=&quot] Monkey, Monkey Sanctuary, 10
Monkey, Monkey Sanctuary2, 10
Bear, Bear Sanctuary, 10
Pimps,Pimp Land,50

Pimps would now have the highest estimated # of species, and using where speciess='Monkey' wouldnt work Sure, I could change that where to Pimp, but that sorta defeats the purpose of sql
[/FONT]
 
Last edited:

DaftPunky

TRIBE Member
if you're only interested in listing the one species with the greatest number, go with this:

select top 1 speciess, sum(estimated_numberss) from TABLE group by speciess order by sum(estimated_numberss) desc
 
tribe cannabis accessories silver grinders

kaniz

TRIBE Member
... duh! obvious
Actually, looking through the other questions, I did that exact same thing for a similar question (select the zoo with the fewest species in the animal table, and did a select top 1 count(distinct species) from eanimal order by count(destinct....)) " .. why I didnt think of doing this for that one, I have no idea.

thanks

I hate it when I over-think problems and come up with overly-complex answers as a result. I knew there had to be a more elegant way of doing it.
 

kaniz

TRIBE Member
wait, no - not so obvious.

What if there are 40 monkeys and 40 bears, - I'd need to list both of them, and not just the top #1.
 

DaftPunky

TRIBE Member
oooh.... touche! I can't believe I didn't think of the problem that way. I'm slipping.

but, to solve your dilemma, try this slight variation of my original solution:

select top 1 with ties speciess, sum(estimated_numberss) from TABLE group by speciess order by sum(estimated_numberss) desc
 
Top