GROUP BY with WHERE?

Hi, I'm trying to write a query to give the following output:
Qualification Registered Passed Failed W/Drawn
MATH 25 15 10 0
OR
MATH Reg 25
MATH Pass 10
MATH Failed 10
I figure I need to select sum(RegistrationID) and group by
Qualification & Outcome but the problem is the number of registered is
the sum of all outcomes, and passed/failed/withdrawn are just for 1
outcome.
Is there any way to get this output? DDL Below.
The Qual names and Outcomes are just numbers in this example.
Thanks
CREATE TABLE [dbo].[_Courses] (
[CourseID] [smallint] NOT NULL ,
[Qualification] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Registrations] (
[RegistrationID] [smallint] NOT NULL ,
[CourseID] [smallint] NOT NULL ,
[CandidateID] [smallint] NOT NULL ,
[Outcome] [tinyint] NOT NULL
) ON [PRIMARY]
GOOops... Additional DDL
ALTER TABLE [dbo].[_Courses] WITH NOCHECK ADD
CONSTRAINT [PK__Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_Registrations] WITH NOCHECK ADD
CONSTRAINT [PK_Registrations] PRIMARY KEY CLUSTERED
(
[RegistrationID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_Registrations] ADD
CONSTRAINT [FK__Registrations__Courses] FOREIGN KEY
(
[CourseID]
) REFERENCES [dbo].[_Courses] (
[CourseID]
)
GO|||What is the relation of Passed, Failed, W/Drawn to Outcome?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1127242417.999335.168990@.g14g2000cwa.googlegroups.com...
Hi, I'm trying to write a query to give the following output:
Qualification Registered Passed Failed W/Drawn
MATH 25 15 10 0
OR
MATH Reg 25
MATH Pass 10
MATH Failed 10
I figure I need to select sum(RegistrationID) and group by
Qualification & Outcome but the problem is the number of registered is
the sum of all outcomes, and passed/failed/withdrawn are just for 1
outcome.
Is there any way to get this output? DDL Below.
The Qual names and Outcomes are just numbers in this example.
Thanks
CREATE TABLE [dbo].[_Courses] (
[CourseID] [smallint] NOT NULL ,
[Qualification] [smallint] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[_Registrations] (
[RegistrationID] [smallint] NOT NULL ,
[CourseID] [smallint] NOT NULL ,
[CandidateID] [smallint] NOT NULL ,
[Outcome] [tinyint] NOT NULL
) ON [PRIMARY]
GO|||There is just another lookup table _Outcome that Links to Outcome on
Registrations. It just holds the names of each outcome 1=Registered,
2=Pass, 3=Fail,4=Withdrawn etc.|||Nothing but guesswork:
SELECT c1.Qualification,
SUM( CASE r1.Outcome WHEN 1 THEN 1 ELSE 0 END ) AS "Registered",
SUM( CASE r1.Outcome WHEN 2 THEN 1 ELSE 0 END ) AS "Pass",
SUM( CASE r1.Outcome WHEN 3 THEN 1 ELSE 0 END ) AS "Fail",
SUM( CASE r1.Outcome WHEN 4 THEN 1 ELSE 0 END ) AS "Withdrawn"
FROM Courses c1
INNER JOIN Registrations r1
ON c1.CourseID = r1.CourseID
GROUP BY c1.Qualification ;
Anith|||Here's an example without pivoting:
select
c.Qualification
, o.OutcomeDescription
, count (r.RegistrationID) as Registrations
from
_Courses c
cross
join
_Outcomes o
left
join
_Registrations r on r.CourseID = c.CourseID
and r.Outcome = o.Outcome
group by
c.Qualification
, o.OutcomeDescription
If you have sample data, we could test better.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1127245679.021677.199090@.g47g2000cwa.googlegroups.com...
There is just another lookup table _Outcome that Links to Outcome on
Registrations. It just holds the names of each outcome 1=Registered,
2=Pass, 3=Fail,4=Withdrawn etc.|||Thanks - both of these work.