INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help writing Count() query

Need help writing Count() query

(OP)
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:

CODE

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

RE: Need help writing Count() query

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...

CODE

; 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

RE: Need help writing Count() query

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

CODE

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

or in the syntax of a CTE:

CODE

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.

RE: Need help writing Count() query

(OP)
Thank you gentlemen!

Lyndon

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close