Help with count and group by

tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--Without clear DDLs ( www.aspfaq.com/5006 ), it is hard to write up a clean
query.
As for a general solution, join the table, derive the age based on the date
of birth column and group by that value with aggregate function COUNT on the
SELECT list. Search the archives of this newsgroup for examples of finding
age from date of birth.
Anith|||try this:
--
this is untested as ddl is not given.
--
SELECT COUNT(DISTINCT VisitID) as nofovisits,
DATEDIFF(YEARS,BIRTHDAY,GETDATE()),COUNT
(distinct ChildID ) FROM tbVisitLog
INNER JOIN tbChild on tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YEARS,BIRTHDAY,GETDATE())
--
Regards
R.D
--Knowledge gets doubled when shared
"Sonny Sablan" wrote:

> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
I see from the names of the pseudo-code tables that (1) you have only
one child, not children (2) They have tuberculosis as shown by the
"tb-" prefix. (3) that these are not tables since they have no keys.
Do know the ISO-11179 naming standards and wehat DDL is?
The specification did not include the dates of the visits, so we cannot
determine what happens as the same child gets older and has visits at
age (n), age (n+1), then skips a year to visit at age (n+3), etc.
Also, a birthdate is a fixed date while a birthday is a month-day pair
that represents a set of event in a lifetime.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Do know the ISO-11179 naming standards and wehat DDL is?
DDL? That sounds a bit like DDT. It's probably poisonous.
Seriously, if you are actually interested in helping people in these
newsgroup, then don't use cryptic stuff like DDL, say CREATE TABLE
statements, so they know what they are talking about.
As for 11179, I would expect not very many care about it, least of all
people who have started to work with SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Sonny,
Its going to be something along these lines i think, hopefully it will give
you a start...
-- gets child ages...
select distinct dateadiff( year, birthday, getdate() )
from tblChild
-- get counts by years...
select ages.age,
count_children = ( select count(*)
from tblChild c
where dateadiff( year, c.birthday,
getdate() ) = ages.age ),
count_visits = ( select count(distinct v.VisitID)
from tblChild c
inner join tblVisitLog v on v.Child
= c.Child
where dateadiff( year, c.birthday,
getdate() ) = ages.age )
from (
select distinct age = dateadiff( year, birthday, getdate() )
from tblChild ) as ages
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
and the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--|||> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2)
A set should be singular, that is more logical in the real world. Having a
single row in a table Children makes no sense.

> They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
This is a practice often used in large systems, it is good to group object
names.
tb[a table]Child[of one or more Child's]
As usual your arrogance is unhelpful, this is a community of both novice and
experts, if you cannot handle that then i would suggest you consider your
contribution [or lack of it].
I notice your book targets people that are both novice and expert, that
makes sense from a commercial perspective - its a pitty you don't adopt that
pose here too; perhaps then you wouldn't have the reputation you have at the
moment which is that of an arrogant tosser.
Only you could interpret birthday in that way, if you read the post you
would understand otherwise.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130077723.236813.131320@.g49g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> I see from the names of the pseudo-code tables that (1) you have only
> one child, not children (2) They have tuberculosis as shown by the
> "tb-" prefix. (3) that these are not tables since they have no keys.
> Do know the ISO-11179 naming standards and wehat DDL is?
> The specification did not include the dates of the visits, so we cannot
> determine what happens as the same child gets older and has visits at
> age (n), age (n+1), then skips a year to visit at age (n+3), etc.
> Also, a birthdate is a fixed date while a birthday is a month-day pair
> that represents a set of event in a lifetime.
>|||This was a big help...
Thank you
--
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uH4Nul$1FHA.3524@.tk2msftngp13.phx.gbl...
> Hi Sonny,
> Its going to be something along these lines i think, hopefully it will
give
> you a start...
> -- gets child ages...
> select distinct dateadiff( year, birthday, getdate() )
> from tblChild
> -- get counts by years...
> select ages.age,
> count_children = ( select count(*)
> from tblChild c
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age ),
> count_visits = ( select count(distinct v.VisitID)
> from tblChild c
> inner join tblVisitLog v on
v.Child
> = c.Child
> where dateadiff( year, c.birthday,
> getdate() ) = ages.age )
> from (
> select distinct age = dateadiff( year, birthday, getdate() )
> from tblChild ) as ages
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:OkVJsQ41FHA.2076@.TK2MSFTNGP14.phx.gbl...
> tbChild
> ChildID | ChildName | Birthday
> tbVisitLog
> VisitID | ChildID |
> I am trying to get the distinct number of 2 year olds, 3 year olds, etc...
> and the total number of visits...
> Example:
> Age | # of Children | # of Visits
> 2 300 350
> 3 500 750
> Sonny
>
> --
> --
>|||I used this query...
SELECT TOP 100 PERCENT tbVisitLog.CenterID,
DATEDIFF(YYYY, tbChild.Birthday, GETDATE()) AS Age,
COUNT(DISTINCT tbVisitLog.ChildID) AS ChildCount,
COUNT(tbVisitLog.ChildID) AS VisitCount
FROM tbVisitLog INNER JOIN
tbChild ON tbVisitLog.ChildID = tbChild.ChildID
GROUP BY DATEDIFF(YYYY, tbChild.Birthday, GETDATE()), tbVisitLog.CenterID
Thanks for the help.
Sonny
--
--
"Sonny Sablan" <sonny@.sablan.org> wrote in message news:OkVJsQ41FHA.2076@.TK2
MSFTNGP14.phx.gbl...
tbChild
ChildID | ChildName | Birthday
tbVisitLog
VisitID | ChildID |
I am trying to get the distinct number of 2 year olds, 3 year olds, etc... a
nd the total number of visits...
Example:
Age | # of Children | # of Visits
2 300 350
3 500 750
Sonny
--
--|||>> A set should be singular, that is more logical in the real world.<<
No, collective nouns -- Forest, not Trees, not Tree. Children, not
Child, not "sons and daughters", etc. A set is a collection and
should be named as such.
And I thought that the "tb-" thing was funny. I also do "vw-" means
"VolksWagen" and get a laugh out of that one.
That is common US English usage. It makes a really big difference. I
had an old Cobol system that only had the birthday, but had pruned the
year out of the birthdate to get it. It was damn useless for anything
but sending a card to someone -- even long after they were dead.
Does the UK or other dialects make them synonyms?