Count how many in year

[Tanja]

On my dog database I want to run a statistic, how many litters was born in a year.

But i can´t get the sum

 

On this testpage you can see in first table (all other is for testing ;-))  which contains the year, the kennelname, date of birth and how many puppys in this litter.

This data i get with

$result = mysql_query("
SELECT 
    dog.dogname, dog.breeder_id, dog.mother_id, dog.date_of_birth, YEAR(dog.date_of_birth) AS year, owner.kennelname,
    COUNT(dog.dogname) anzahl
FROM 
    dog

LEFT JOIN owner ON
(dog.breeder_id = owner.id)

WHERE owner.country_short='de' 
AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 20 YEAR)
AND owner.kennel_note =''
GROUP BY 
    dog.mother_id, dog.date_of_birth
ORDER BY 
    date_of_birth ASC 

", $db) OR die(mysql_error());

 while($row = mysql_fetch_array($result)) ...

to get all puppys of one year, I change the GROUP BY to year - works also fine.

But how can I get the sum of litters / year?

In Year 1997 it should be 2, in 1998 0, in 1999 2, in 2000 1 and so on.

 

The Array (reduced) looks following:

Array
(
    [0] => 1997
    [year] => 1997
)

Array
(
    [0] => 1997
    [year] => 1997
)

Array
(
    [0] => 1999
    [year] => 1999
)

Array
(
    [0] => 1999
    [year] => 1999
)

Array
(
    [0] => 2000
    [year] => 2000
)

I don´t care how the sum is calculate (sql/php or other), most important is to get it.

[Tanja]

Problem solved ;-)

SELECT *, COUNT(*) FROM
(
SELECT 
    YEAR(dog.date_of_birth) AS year
    
FROM 
    dog

LEFT JOIN owner  ON
(dog.breeder_id = owner.id)

WHERE owner.country_short='de' 
AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 20 YEAR)
AND owner.kennel_note =''
GROUP BY 
    dog.mother_id, dog.date_of_birth, year 
) AS T1
GROUP BY year

[Jessica]

No, add the year into your original group buc

[Jessica]

Group by

[Tanja]

If I group by year instaed of dog.mother_id, dog.date_of_birth, year i get "only" all puppies in sum of the year, but not the sum of litters.

 

Take a look (Countrys are not working here) , at this moment only in german. Anzahl Welpen means Puppies at all,  Anzahl Würfe means Litters at all and at the end there is a list with all dates.

 

Hmmm....

[Jessica]

In addition to. Not instead of.

[Barand]

You doing a LEFT JOIN to the owner table so any selection conditions on the owner table need to be in the JOIN ON clause and not in the WHERE clause

    LEFT JOIN owner  ON
        (dog.breeder_id = owner.id)
        AND owner.country_short='de'
        AND owner.kennel_note ='' 

[Tanja]

@Jessica: i can put year in the group by in addition, but no count of litters will be done.

@Barand: if i change my query in this way i get wrong counts (for Germany i can take a look in a real book, so i know that the calculation is right).

 

I don´t know why it is so difficult to get the sum which is already at the ground in array. In array i can´t count all 2008....

 

By the way, is my solution bad?

[Barand]

The LEFT JOIN will include all dogs, even those with no owner on file. INNER JOIN will include only dogs with owners.

 

Grouping by year within date of birth is pointless - there is only a single year in a date