Darn, it's not working after all.
SELECT
people.*,
COUNT ( DISTINCT cars.car_id ) AS car_count,
COUNT ( DISTINCT pets.pet_id ) AS pet_count,
SUM ( IF ( pets.date_bought > NOW() - INTERVAL 7 DAY, 1, 0 ) ) AS
new_pet_count
WHERE...etc
car_count and pet_count are calculating correctly, but new_p
Yes, I don't see why that wouldn't work...
On Mon, Nov 2, 2009 at 11:09 AM, Brian Dunning wrote:
> Johnny - Your solution might actually help me solve my next step, which is
> to also return a count of pets bought only within the last 7 days. Something
> like this:
>
>
>
>> SELECT people.*,
>>
Johnny - Your solution might actually help me solve my next step,
which is to also return a count of pets bought only within the last 7
days. Something like this:
SELECT people.*,
SUM(IF(cars.id IS NULL,0,1)) AS car_count,
SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
SUM ( IF ( pets.d
Thanks, this solved it!
On Nov 2, 2009, at 12:37 AM, Michael Dykman wrote:
I suspect 'distinct' might help you out here.
SELECT
people.*,
count(distinct cars.car_id) as car_count,
count(distinct pets.pet_id) as pet_count
--
MySQL General Mailing List
For list archives: htt
You are asking for all records form all tables. So, If 1 person has 1 car
and 1 pet, there will be 2 records returned for that 1 person.
You'll need to use SUM() instead of COUNT():
SELECT people.*,
SUM(IF(cars.id IS NULL,0,1)) AS car_count,
SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
Ma
Hi all -
I have a table of PEOPLE, and a table of CARS owned by various people,
and a table of PETS owned by various people. Each person may have 0 or
more pets, and each person may have 0 or more cars. I'm trying to
return a list of all the people, showing how many pets each person
has,
