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?

Help with Calculated member

I'm trying to create a calculate member in SQL Server 2005 that will return a distinct count of clients year to date for all dates within my time dimension. I have figured out that if I create a measure that returns a distinct count then I can use the aggregate function and YTD function to get the distinct count YTD for any date. However, the aggregate function requires a slicer dimension (i think) and every time I put the mdx into the expression box, it does not like the syntax.

I have tried this:

With member [Authorization Service Date].[Calendar].[BlaBla] as

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

and I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

returns error: Error 1 MdxScript(DSS) (67, 4) The function can be called only on a named set. 0 0

I don't know wht this means

Any help would be deeply appreciated

I think you are looking to use the CURRENTMEMBER function instead of CURRENT.

I think this will simply take you to your next challenge. If you run this query, you'll likely get NULL for your result.

I think something like this will better serve your needs:

Code Snippet

With member [Measures].[x] as

COUNT(

EXISTS(

[Customer].[Customer].[Customer].Members,

YTD([Delivery Date].[Calendar].CurrentMember),

'Internet Sales'

)

)

select

[Measures].[x] on columns

from [Adventure Works]

where [Delivery Date].[Date].[June 1, 2002]

;

|||

Bryan,

I appreciate your taking your time to assist me with this. Unfortunately, it did not work. As translated, your mdx on my system looks like

with member [Measures].[x] as

COUNT(

EXISTS(

[CCE Members].[MemberId].Members,

YTD([Authorization Service Date].[Calendar].CurrentMember),

'Authorization Service Days Count'

)

)

SELECT [Measures].[x] ON 0

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

I get xError

Just for your info however, I know that all of the underlying attributes and metrics join up properly becuase when I execute:

SELECT [Measures].[Authorization Service Days Count] ON 0,

[CCE Members].[MemberId].Members ON 1

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

it works.

Again, your assistance is trully appreciated.

Thanks,

Michael

|||

Two things I noticed in the EXISTS function. Try changing the set to include just the leaf level members:

[CCE Members].[MemberId].Members --> [CCE Members].[MemberId].[MemberId].Members

Also, is 'Authorization Service Days Count' the name of the measure or the measure group? You need to identify the measure group.

B.

sql

Help with a Query

Hi,
I have a problem with a query.
Select distinct count(*)
from
Products B1,
categoryProducts B2
where
B1.ProductID = B2.ProductID and
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE
CategoryID=4)
)
This query finds all of the CategoryID's from the Categories table, whose
base CategoryID is 4. The table structure of Categories is:
Categories
==========
CategoryID int
Name
Linkage varchar(100)
Data:
CategoryID Name Linkage
...
4 Mind. Body & Spirit 4.1
5 Spiritualism 4.1.1
6 Occult 4.2.2
7 Religion 4.2.3
8 Christianity 4.2.3.1
9 Islam 4.2.3.2
I have a book product which has been categorised into three sub-categories,
using a table called CategoryProducts.
Products
========
ProductID int
Title varcahr(250)
CategoryProducts
================
CategoryID int
ProductID int
Products Data:
-----
ProductID Title
1 The Sacred and the Profane
CategoryProducts Data:
-------
CategoryID ProductID
4 1
5 1
7 1
So The Sacred and the Profane has been categorised into 3 categories, all
falling within or under Mind. Body & Spirit. When I execute the query at
the top here, I get 3 results - this is correct. However, I only want 1
item, since they are all the same book. I was hoping the distinct SQL
keyword would work, but it doesnt, and I am lost - help!
jr.
I'm not sure I understand your query as it uses a count. I assume that was a test and you are really using something like:
Select distinct *
from ...
The asterisk will return all fields from BOTH tables which will be three separate records.
Instead, try something like:
Select distinct Products.ProductID, Products.Title
from ...
That should just give you the single record you want.
HTH.|||Hi,
Thanks for your suggestion, it works using the distinct the way yousuggest. I didnt explain myself correctly ealier, as I do needthe count(*). As part of my paging routine, the first step is tofind out how many records are available in the complete query, beforepaging is used, so I can say "Viewing 1 to 10 of 2000 records". So I need to count the contents of the full dataset using the links tothe categories using the materialized path. I managed to drop theProducts table as it wasnt adding anything to calculate the count value.
Select distinct count(B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
The CategoryProducts table does have 3 entries in it, with differentcategory ids but the same product id. I cannot work out why asking toreturn the distinct on the single product id would give only 1 recordrather than 3. Any thoughts?
thanks,
jr.
|||ZLA,
I worked it out - I had a post on the MS boards, and it was highlightedto me that I need to put the distinct inside the count():
Select distinct count(distinct B2.ProductID)
from
categoryProducts B2
where
B2.CategoryID in (
SELECT categoryID FROM Categories WHERE
Linkage LIKE (SELECT Linkage+'%' FROM Categories WHERE CategoryID=4)
)
Thanks for your time in looking at this problem.
jr.