Derived Table Problem

[Bill]

I've created this:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

and I get this:

ProjectID Registrants Submissions
--------- ----------- -----------
adv_1046 99 99
adv_1047 185 185
adv_1105 66 66
boh_1071 34 34

Instead, I want this:

ProjectID Registrants Submissions
--------- ----------- -----------
adv_1046 99 14
adv_1047 185 82
adv_1105 66 17
boh_1071 34 12

The "ProjectID" and "Submission s" columns are produced when I run the
derived table (dt, above) as a standalone query. By the same token,
the "Project ID" and "Registrant s" columns are produced when I run the
"outer" query, above.

Am I on the right track here?

TIA,

-- Bill

[Erland Sommarskog]

Re: Derived Table Problem

[posted and mailed, please reply in news]

Bill (w.white@snet.n et) writes:[color=blue]
> I've created this:
>
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID[/color]

COUNT(dt.Hits) returns the number of rows where this column is not null.
I would guess that you want SUM(dt.Hits) here instead.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bill]

Re: Derived Table Problem

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9462F1 3D69581Yazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Bill (w.white@snet.n et) writes:[color=green]
> > I've created this:
> >
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID[/color]
>
> COUNT(dt.Hits) returns the number of rows where this column is not null.
> I would guess that you want SUM(dt.Hits) here instead.[/color]

Using SUM(dt.Hits) yields:

ProjectID Registrants Submissions
--------- --- ----
adv_1046 99 1881
adv_1047 185 2960
adv_1105 66 462
boh_1071 34 952
boh_1122 38 608
boh_1136 37 444
brw_1065 44 1012

which I suspect is closer to my desired result, since the value in the
Submissions column = (Registrants * Submissions) for that ProjectID;
so the proper Submissions value is "in there somewhere". My need is
for the correct Submissions value to appear within the Submissions
column:

ProjectID Registrants Submissions
--------- --- ----
adv_1046 99 19
adv_1047 185 16
adv_1105 66 7
boh_1071 34 28
boh_1122 38 16
boh_1136 37 12
brw_1065 44 23

Happy New Year!

-- Bill

[Erland Sommarskog]

Re: Derived Table Problem

Bill (w.white@snet.n et) writes:[color=blue]
> Using SUM(dt.Hits) yields:
>
> ProjectID Registrants Submissions
> --------- --- ----
> adv_1046 99 1881
> adv_1047 185 2960
> adv_1105 66 462
> boh_1071 34 952
> boh_1122 38 608
> boh_1136 37 444
> brw_1065 44 1012
>
> which I suspect is closer to my desired result, since the value in the
> Submissions column = (Registrants * Submissions) for that ProjectID;
> so the proper Submissions value is "in there somewhere". My need is
> for the correct Submissions value to appear within the Submissions
> column:
>
> ProjectID Registrants Submissions
> --------- --- ----
> adv_1046 99 19
> adv_1047 185 16
> adv_1105 66 7
> boh_1071 34 28
> boh_1122 38 16
> boh_1136 37 12
> brw_1065 44 23[/color]

Indeed it seems that diving the Submissions column with the Registratns
column gives the result you are asking for. That is:

SUM(dt.Hits) / COUNT(c.ID)

Moral: when you ask a question like this, it is always a good idea to
provide:

o CREATE TABLE statements of the involved tables.
o INSERT statements with sample data.
o The desired output given the sample.

With this infomation, anyone who takes a stab with your problem can post a
tested solution. Without this information, the answer you get is more or
less guesswork.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bill]

Re: Derived Table Problem

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9463F1 E595160Yazorman @127.0.0.1>...[color=blue]
> Bill (w.white@snet.n et) writes:[color=green]
> > Using SUM(dt.Hits) yields:
> >
> > ProjectID Registrants Submissions
> > --------- --- ----
> > adv_1046 99 1881
> > adv_1047 185 2960
> > adv_1105 66 462
> > boh_1071 34 952
> > boh_1122 38 608
> > boh_1136 37 444
> > brw_1065 44 1012
> >
> > which I suspect is closer to my desired result, since the value in the
> > Submissions column = (Registrants * Submissions) for that ProjectID;
> > so the proper Submissions value is "in there somewhere". My need is
> > for the correct Submissions value to appear within the Submissions
> > column:
> >
> > ProjectID Registrants Submissions
> > --------- --- ----
> > adv_1046 99 19
> > adv_1047 185 16
> > adv_1105 66 7
> > boh_1071 34 28
> > boh_1122 38 16
> > boh_1136 37 12
> > brw_1065 44 23[/color]
>
> Indeed it seems that diving the Submissions column with the Registratns
> column gives the result you are asking for. That is:
>
> SUM(dt.Hits) / COUNT(c.ID)
>
> Moral: when you ask a question like this, it is always a good idea to
> provide:
>
> o CREATE TABLE statements of the involved tables.
> o INSERT statements with sample data.
> o The desired output given the sample.
>
> With this infomation, anyone who takes a stab with your problem can post a
> tested solution. Without this information, the answer you get is more or
> less guesswork.[/color]

Alrighty, then! Here we go:

CREATE TABLE CME_TBL_dev
(
ID int IDENTITY (1, 1) NOT NULL,
ProjectID varchar (50) NULL,
registrationDat e datetime NULL DEFAULT (getdate()),
lastName varchar (60) NULL,
testDate datetime NULL,
evalDate datetime NULL
)

----------------------------------------------

INSERT INTO CME_TBL_dev
(
ProjectID,
lastName,
testDate,
evalDate
)

SELECT 'pmw_1129', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'pmw_1129', 'wilkins', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'pmw_1129', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'pmw_1129', 'fife', NULL, NULL
UNION ALL
SELECT 'pmw_1129', 'fonebone', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'brw_1065', 'wilkins', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'brw_1065', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'brw_1065', 'fife', NULL, NULL
UNION ALL
SELECT 'brw_1065', 'fonebone', NULL, NULL
UNION ALL
SELECT 'any_8930', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'any_8930', 'wilkins', NULL, NULL
UNION ALL
SELECT 'any_8930', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'any_8930', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'any_8930', 'fife', NULL, NULL
UNION ALL
SELECT 'any_8930', 'fonebone', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'hir_1093', 'wilkins', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'hir_1093', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'hir_1093', 'fife', NULL, NULL
UNION ALL
SELECT 'hir_1093', 'fonebone', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'smythe', '11/1/03', NULL
UNION ALL
SELECT 'yth_9804', 'wilkins', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'hammarskjold', NULL, '11/8/03'
UNION ALL
SELECT 'yth_9804', 'moosejuice', '11/11/03', '11/18/03'
UNION ALL
SELECT 'yth_9804', 'fife', NULL, NULL
UNION ALL
SELECT 'yth_9804', 'fonebone', NULL, NULL

------------------------------------------------
-- This is the query I'm hoping I can get to yield
-- the desired results (see below).

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID

ORDER BY
c.ProjectID

--------------------------
-- The following two queries are for utility purposes.

SELECT
c.ProjectID, Count(c.ID) as 'Registrants'
FROM
CME_TBL_dev c
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

---------------------------

SELECT
c.ProjectID, Count(c.ID) as 'Submissions'
FROM
CME_TBL_dev c
WHERE
c.evalDate Is Not NULL OR
c.testDate Is Not NULL
GROUP BY
c.ProjectID
ORDER BY
c.ProjectID

--------------------------------------

What I seek is this:

ProjectID Registrants Submissions
--------- ----------- -----------
any_8930 6 3
brw_1065 6 3
hir_1093 6 3
pmw_1129 6 3
yth_9804 6 3


But what I get instead is this (per the derived table query above):

ProjectID Registrants Submissions
--------- ----------- -----------
any_8930 6 6
brw_1065 6 6
hir_1093 6 6
pmw_1129 6 6
yth_9804 6 6

Solving this would have major positive impact on many aspects of my
reporting efforts.

Thanks in advance!

-- Bill

[Erland Sommarskog]

Re: Derived Table Problem

Bill (w.white@snet.n et) writes:[color=blue]
> -- This is the query I'm hoping I can get to yield
> -- the desired results (see below).
>
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID
>...
> What I seek is this:
>
> ProjectID Registrants Submissions
> --------- ----------- -----------
> any_8930 6 3
> brw_1065 6 3
> hir_1093 6 3
> pmw_1129 6 3
> yth_9804 6 3[/color]

It does indeed seem that my suggest to take sum divided by count
gives the desired result:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
FROM
CME_TBL_dev c
JOIN
(SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt
ON c.ProjectID = dt.ProjectID
GROUP BY c.ProjectID
ORDER BY c.ProjectID

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Bill]

Re: Derived Table Problem

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9464EE 208FB4EYazorman @127.0.0.1>...[color=blue]
> Bill (w.white@snet.n et) writes:[color=green]
> > -- This is the query I'm hoping I can get to yield
> > -- the desired results (see below).
> >
> > SELECT
> > c.ProjectID,
> > Count(c.ID) as 'Registrants',
> > Count(dt.Hits) as 'Submissions'
> > FROM
> > CME_TBL_dev c
> > JOIN
> > (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> > WHERE evalDate Is Not NULL OR testDate Is Not NULL
> > GROUP BY ProjectID
> > ) dt
> > ON c.ProjectID = dt.ProjectID
> > GROUP BY c.ProjectID
> > ORDER BY c.ProjectID
> >...
> > What I seek is this:
> >
> > ProjectID Registrants Submissions
> > --------- ----------- -----------
> > any_8930 6 3
> > brw_1065 6 3
> > hir_1093 6 3
> > pmw_1129 6 3
> > yth_9804 6 3[/color]
>
> It does indeed seem that my suggest to take sum divided by count
> gives the desired result:
>
> SELECT
> c.ProjectID,
> Count(c.ID) as 'Registrants',
> SUM(dt.Hits) / Count(dt.Hits) as 'Submissions'
> FROM
> CME_TBL_dev c
> JOIN
> (SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
> WHERE evalDate Is Not NULL OR testDate Is Not NULL
> GROUP BY ProjectID
> ) dt
> ON c.ProjectID = dt.ProjectID
> GROUP BY c.ProjectID
> ORDER BY c.ProjectID[/color]

Solved it! I took a different approach. I think the crux of my
difficulty lay in "overprocessing " dt.Hits:

SELECT
c.ProjectID,
Count(c.ID) as 'Registrants',
dt.Hits as 'Submissions'
FROM
CME_TBL_dev c,
(
SELECT ProjectID, Count(*) as Hits FROM CME_TBL_dev
WHERE evalDate Is Not NULL OR testDate Is Not NULL
GROUP BY ProjectID
) dt

WHERE
c.ProjectID = dt.ProjectID

GROUP BY
c.ProjectID, dt.Hits

ORDER BY
c.ProjectID

Yields:

ProjectID Registrants Submissions
--------- ----------- -----------
any_8930 6 3
brw_1065 6 3
hir_1093 6 3
pmw_1129 6 3
yth_9804 6 3


Thanks for priming my mental pump!

-- Bill