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_104699 99
adv_1047185 185
adv_110566 66
boh_107134 34
Instead, I want this:
ProjectID Registrants Submissions
--- ---- ----
adv_104699 14
adv_1047185 82
adv_110566 17
boh_107134 12
The "ProjectID" and "Submissions" columns are produced when I run the
derived table (dt, above) as a standalone query. By the same token,
the "Project ID" and "Registrants" columns are produced when I run the
"outer" query, above.
Am I on the right track here?
TIA,
-- Bill[posted and mailed, please reply in news]
Bill (w.white@.snet.net) writes:
> 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
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/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9462F13D69581Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Bill (w.white@.snet.net) writes:
> > 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
> 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.
Using SUM(dt.Hits) yields:
ProjectID Registrants Submissions
--- -- --
adv_104699 1881
adv_1047185 2960
adv_110566 462
boh_107134 952
boh_112238 608
boh_113637 444
brw_106544 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_104699 19
adv_1047185 16
adv_110566 7
boh_107134 28
boh_112238 16
boh_113637 12
brw_106544 23
Happy New Year!
-- Bill|||Bill (w.white@.snet.net) writes:
> 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
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/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9463F1E595160Yazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > 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
> 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.
Alrighty, then! Here we go:
CREATE TABLE CME_TBL_dev
(
ID int IDENTITY (1, 1) NOT NULL,
ProjectID varchar (50) NULL,
registrationDate 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_89306 3
brw_10656 3
hir_10936 3
pmw_11296 3
yth_98046 3
But what I get instead is this (per the derived table query above):
ProjectID Registrants Submissions
--- ---- ----
any_89306 6
brw_10656 6
hir_10936 6
pmw_11296 6
yth_98046 6
Solving this would have major positive impact on many aspects of my
reporting efforts.
Thanks in advance!
-- Bill|||Bill (w.white@.snet.net) writes:
> -- 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
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/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9464EE208FB4EYazorman@.127.0.0.1>...
> Bill (w.white@.snet.net) writes:
> > -- 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
> 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
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
No comments:
Post a Comment