CODEWITHSUNDEEP
mysqlsqlsuminner-join
Mark Gilchrist
Mark Gilchrist

Reputation: 2032

inner join and sum()

I Have two tables trips_data which as tripid, userid, species (int),killcount masterspecies which had species_id and speceies (string)

I am trying to retrieve a list of all species seen on a trip I am hoping to get 

sum(killcount) : tripid :species (string):species (int)
57                 300    rabbit            1
2                  300    foxes             2 
1                  300    squirels          8

and so on
i have the below query which returns everything I want except the sum(killcount) is about 8000 when it should be 57.

Any help would be hugely apreciated

SELECT sum(trips_data.killcount), 
  trips_data.species,trips_data.spceces,
  masterspecies.species 
from trips_data 
join masterspecies 
WHERE tripid=$tripid 
   AND userid=1 
   AND NOT killcount=0

Upvotes: 1

Views: 79

Answers (2)

criticalfix
criticalfix

Reputation: 2870

This is a cartesian join:

from trips_data join masterspecies

This will return a record for every combination of records from the two tables. That is usually not the intention. Join conditions look something like this:

from trips_data
join masterspecies
on masterspecies.species_id = trips_data.species_id

This will match the records up and only return matching records, so there is a chance your sum will come out correctly.

Upvotes: 1

kielni
kielni

Reputation: 5009

You need to tell the database how to join; otherwise you're getting every possible combination. It looks like trips_data.species should match master_species.species_id; is that right? You also need to group the results by species.

SELECT sum(trips_data.killcount), trips_data.species, masterspecies.species 
from trips_data join masterspecies
WHERE tripid=$tripid AND userid=1 and trips_data.species=masterspecies.species_id
group by trips_data.species, masterspecies.species;

Upvotes: 2

Related Questions