Help with SQL Statement ... ordering.

jtown

Ok say I have one table of animals ... say it's called ANIMALS

ID/Common Name/Species/Category/Etc.

Now I have another table of inventory of these animals in another table .... say it's called ANIMALINVENTORY

ID/AnimalID(filled with ANIMAL.id's)/CageLocation/SeenVet/Etc.

Now, I have a table of animals that shows only a certain Category at one time in a table. I can sort by species name (easy), but I was wondering if you guys know how I would go about ordering by the count in ANIMALINVENTORY from say greatest to least.

Any help would be appreciated. I hope my question is clear enough. I can do it with arrays and whatever, but I'm wondering if there's a single sql statement way.

chads2k2

"I was wondering if you guys know how I would go about ordering by the count in ANIMALINVENTORY from say greatest to least. "

Based on what? Do you want it to be grouped or what? Give an example of what you are looking for ... like the output... That would be easier to work with...everything else you provided is great!

I think I know how to solve your system...just need that bit of info.

Chad

jtown

ok so I want a table like this

COMMON NAME | SPECIES | NUMBER OF ANIMALS (in ANIMAL INVENTORY)

nnichols

ANIMALS
ID/CommonName/Species/Category/Etc.

ANIMALINVENTORY
ID/AnimalID(filled with ANIMAL.id's)/CageLocation/SeenVet/Etc.

COMMON NAME | SPECIES | NUMBER OF ANIMALS (in ANIMAL INVENTORY)

dog | whatever | 134
cat | whatever | 120
birds | blah | 50
rats | blah | 20

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num
FROM ANIMALS, ANIMALINVENTORY
WHERE ANIMALS.ID = ANIMALINVENTORY.AnimalID
GROUP BY ANIMALINVENTORY.AnimalID
ORDER BY num DESC

Hope this helps 😉

jtown

Originally posted by nnichols

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num
FROM ANIMALS, ANIMALINVENTORY
WHERE ANIMALS.ID = ANIMALINVENTORY.AnimalID
GROUP BY ANIMALINVENTORY.AnimalID
ORDER BY num DESC

Hope this helps 😉

you rule!

is there any way to get it to output entries that come back as a 0 in count? right now it only shows ones it can count.

jtown

Originally posted by jtown

you rule!

is there any way to get it to output entries that come back as a 0 in count? right now it only shows ones it can count.

hmm ... nevermind, that's impossible the way the query is working.

I can do something else though. thanks!

nnichols

change the join to a LEFT JOIN then it will return the 0s as well -

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num
FROM ANIMALS
LEFT JOIN ANIMALINVENTORY
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID
GROUP BY ANIMALS.ID
ORDER BY num DESC

I think that will work :rolleyes:

jtown

actually ya, is there a way to get the animals that have 0 inventory? It doesn't have to be in the same sql statement, but it can be another one where you get only the ones that do not exist in the ANIMALINVENTORY DB.

Thanks!

nnichols

did it work the way you wanted??

jtown

Originally posted by nnichols
did it work the way you wanted??

hmm ... nope, I got 0 returned results.

nnichols

Did it return an empty result set or an error? If error, what error?

I just checked it with some dummy data and it works fine.

jtown

Originally posted by nnichols
Did it return an empty result set or an error? If error, what error?

I just checked it with some dummy data and it works fine.

hmm really? I get no error, no complaints, but it just returns empty. one problem that it may be is that the example I used is a simplified version of what I need to do. I'll look it over and if you say it works, it should.

I'll get back to you in a few.

jtown

ok to give you the full problem, say I have different categories of animals ... say Big and Small and I also want to only show the big and small ones (ie Animals.type='Big') as well as the other conditions above.

I'm playing around with the ordering, but this is the first time I've used left join so I'm trying to fully understand it.

Lemme know if you have any input and thank you very much for helping me out.

jtown

here's what I've been trying ...

SELECT CommonName, Species, COUNT(ANIMALINVENTORY.AnimalID) AS num
FROM ANIMALS
LEFT JOIN ANIMALINVENTORY
ON ANIMALS.ID = ANIMALINVENTORY.AnimalID
WHERE ANIMALS.TYPE = 'BIG'
GROUP BY ANIMALS.ID
ORDER BY num DESC

but it stops after the first 0 it finds for some reason.

nnichols

The query you have should work fine. I tried it against my test data with no problems.

If you are still having problems post a small dump (zipped) of the two tables (+ structures) and the exact query that you are trying.

jtown

Originally posted by nnichols
The query you have should work fine. I tried it against my test data with no problems.

If you are still having problems post a small dump (zipped) of the two tables (+ structures) and the exact query that you are trying.

ahhh ... minor error on my part. thank you very much for your help.

😃 sweet!