More help with count and group by

Using these tables
tbFamily
FamilyID | FamilyName
tbChild
ChildID | FamilyID | ChildName | Birthday | etc...
tbVisitLog
VisitID | ChildID | DateTimeIn | etc...
I am trying to get how many children visited once, twice, three times, four
times and five times.
Example return recordset where the total # of visits is 400:
Freq | # of Children
1 200
2 100
3 75
4 23
5 2
Thanks in advance
Sonny
--Try:
select
Visits
, count (*) as Freq
from
(
select
ChildID
, count (*) as Visits
from
tbVisitLog
group by
ChildID
) as x
group by
Visits
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:OFQnQiC2FHA.3180@.TK2MSFTNGP14.phx.gbl...
Using these tables
tbFamily
FamilyID | FamilyName
tbChild
ChildID | FamilyID | ChildName | Birthday | etc...
tbVisitLog
VisitID | ChildID | DateTimeIn | etc...
I am trying to get how many children visited once, twice, three times, four
times and five times.
Example return recordset where the total # of visits is 400:
Freq | # of Children
1 200
2 100
3 75
4 23
5 2
Thanks in advance
Sonny
--|||Sorry Tom, but I think you wrong... Client wants statistics not for single
Child, his interest number of children visited at the SAME time & how many
times this happens.
endorsed by signature
*** Serg Yury ***
"Tom Moreau" <tom@.dont.spam.me.cips.ca> /
: news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
> Try:
> select
> Visits
> , count (*) as Freq
> from
> (
> select
> ChildID
> , count (*) as Visits
> from
> tbVisitLog
> group by
> ChildID
> ) as x
> group by
> Visits
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
>|||Hopefully, he can clarify the spec for us.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c2312$1_1@.isa.dvgd.mps...
> Sorry Tom, but I think you wrong... Client wants statistics not for single
> Child, his interest number of children visited at the SAME time & how many
> times this happens.
> --
> endorsed by signature
> *** Serg Yury ***
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> /
> : news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
>
>|||Ok.
Sonny, do you want to clarify?
endorsed by signature
*** Serg Yury ***
"Tom Moreau" <tom@.dont.spam.me.cips.ca> /
: news:elvAC7C2FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hopefully, he can clarify the spec for us.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
> news:435c2312$1_1@.isa.dvgd.mps...
single
many|||Yes...
I want to know how many children visited once...
And how many children visited twice... who did not visit once...
And how many children visited three times... who did not visit once or
twice...
Sonny
--
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c30cf$1_1@.isa.dvgd.mps...
> Ok.
> Sonny, do you want to clarify?
> --
> endorsed by signature
> *** Serg Yury ***
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> /
> : news:elvAC7C2FHA.1028@.TK2MSFTNGP12.phx.gbl...
> single
> many
>
>|||This didn't return what I need...
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O0AmekC2FHA.908@.tk2msftngp13.phx.gbl...
> Try:
> select
> Visits
> , count (*) as Freq
> from
> (
> select
> ChildID
> , count (*) as Visits
> from
> tbVisitLog
> group by
> ChildID
> ) as x
> group by
> Visits
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:OFQnQiC2FHA.3180@.TK2MSFTNGP14.phx.gbl...
> Using these tables
> tbFamily
> FamilyID | FamilyName
> tbChild
> ChildID | FamilyID | ChildName | Birthday | etc...
> tbVisitLog
> VisitID | ChildID | DateTimeIn | etc...
> I am trying to get how many children visited once, twice, three times,
four
> times and five times.
> Example return recordset where the total # of visits is 400:
> Freq | # of Children
> 1 200
> 2 100
> 3 75
> 4 23
> 5 2
> Thanks in advance
> Sonny
> --
>|||Am I going to have to create separate queries for each frequency count...
This:
SELECT COUNT(VisitLogID), COUNT(DISTINCT ChildID)
FROM tbVisitLog
gives me the total visits and the total of unique children visiting.
I would be satisfied with help on how to find out how many children visited
twice.
--
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:uOF5UOE2FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yes...
> I want to know how many children visited once...
> And how many children visited twice... who did not visit once...
> And how many children visited three times... who did not visit once or
> twice...
> Sonny
> --
> --
> "Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
> news:435c30cf$1_1@.isa.dvgd.mps...
how
>|||Ok. It's clearly expressed...
select Visits as Freq,count(*) as '# of Children'
from
(
select ChildID,count(*) as Visits from tbVisitLog group by ChildID
) v
group by Visits
order by Visits desc
endorsed by signature
*** Serg Yury ***
"Sonny Sablan" <sonny@.sablan.org> / :
news:uNz3emE2FHA.3660@.TK2MSFTNGP15.phx.gbl...
> Am I going to have to create separate queries for each frequency count...
>
> This:
> SELECT COUNT(VisitLogID), COUNT(DISTINCT ChildID)
> FROM tbVisitLog
> gives me the total visits and the total of unique children visiting.
> I would be satisfied with help on how to find out how many children
visited
> twice.
>
> --
> --
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:uOF5UOE2FHA.3256@.TK2MSFTNGP09.phx.gbl...|||Ahh Yes...
That's what I need.
Thank You
Sonny
--
--
"Syu" <SYamshchikov@.ivc.dvgd.mps> wrote in message
news:435c5bf3$1_1@.isa.dvgd.mps...
> Ok. It's clearly expressed...
> select Visits as Freq,count(*) as '# of Children'
> from
> (
> select ChildID,count(*) as Visits from tbVisitLog group by ChildID
> ) v
> group by Visits
> order by Visits desc
> --
> endorsed by signature
> *** Serg Yury ***
> "Sonny Sablan" <sonny@.sablan.org> / :
> news:uNz3emE2FHA.3660@.TK2MSFTNGP15.phx.gbl...
count...
> visited
>
>sql