Need help writing Count() query 2

[LyndonOHRC]

I have a table of horse birth records. One column has the DOBN number (Birth Mother ID), and another has the BYR (birth year).

We need to know some stats about multiple births. I need to know, by birth year, how many dams had two foals, how many had 3...

Output:
Year foalQty, MareCount
2007 2 37
2007 3 19
...

So, in 2007 37 dams had two babies, 19 dams had 3 babies...

What I have so far, this gives me the foal count for each mare, this is where I'm stuck:

Select distinct dobn,  count(dobn)as foalCount, byr
From form100 
Where byr>='2007' and breed='2'
Group by dobn, byr
Order by dobn, byr

Any help much appreciated...


Lyndon

 

[gmmastros]

I think you should use the having clause. Think of the having clause as a where clause for aggregates. Also, you seem to need a count of the counts. There are several ways to do this, I prefer using a common table expression (CTE), like this...

; With Births As
(
  Select dobn,  count(dobn)as foalCount, byr
  From   form100 
  Where  byr >= '2007' 
         and breed='2'
  Group by dobn, byr
  having Count(bobn) > 1
)
Select byr,
       foalCount,
       Count(*) As MareCount
From   Births
Group By byr, foalCount
Order by byr, foalCount

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

That's a good start, as next step you want to group by the foalcount and count the number of mares having had that same foalCount, this simply needs nested queries, you can't get this in one go, as you first need the foalCount, then can group by that for the final aggregation of data.

Without testing

SELECT byr, foalCount, COUNT(*) as MareCount FROM (yourquery) tmp Group by tmp.byr, tmp.foalCount

or in the syntax of a CTE:

With foalspermare as (
Select dobn, count(dobn)as foalCount, byr
From form100 
Where byr>='2007' and breed='2'
Group by dobn, byr
Order by dobn, byr)

Select byr, foalCount, COUNT(*) as MareCount FROM foalspermare Group By byr, foalCount

Bye, Olaf.

 

[LyndonOHRC]

Thank you gentlemen!

Lyndon