Help with counting query

This is a counting issue following an inner join.
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:

>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:

> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:

> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>