January 23, 2007 at 12:08 pm
Hello,
In a Group By querry I need to get a column value (Enrolled) from each of the clusters which will tell me if all of the rows are not enrolled. The column "Enrolled" tells me weather a row is enrolled or not. IS there an aggregate function which will tell me if the values of a column are all the same (Not Enrolled) or not (At least one IS enrolled)?
SELECT
COUNT(*) AS "COUNT", CAST(StartTimeOrdinal AS INT), CAST(PerformingDayOrdinal AS INT),Enrolled(are all of the rows in the group not enrolled?)
FROM OpsSchedule WHERE PerformingDayOrdinal IS NOT NULL
GROUP BY StartTimeOrdinal, PerformingDayOrdinal
ORDER BY PerformingDayOrdinal, StartTimeOrdinal
January 23, 2007 at 4:05 pm
Hi Brian,
I can think of one way of doing this off the top of my head but it might not be the best way but somthing along the lines of the following might work:
CREATE TABLE enrolledTest
(
id INT IDENTITY (1, 1),
groupCol INT,
startOrd INT,
enrolled INT
)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 2, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 2, 0)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (1, 2, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (2, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (2, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (2, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (2, 1, 0)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 1, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 2, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 2, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 2, 0)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 3, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 3, 1)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 4, 0)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 4, 0)
INSERT INTO enrolledTest (groupCol, startOrd, enrolled) VALUES (3, 4, 0)
SELECT
[COUNT],
groupCol,
startOrd,
CASE WHEN [EnrolledSum] >= 1 THEN 1 ELSE 0 END [AtLeastOneEnrolled],
CASE WHEN [EnrolledSum] = [Count] THEN 1 ELSE 0 END [AllEnrolled]
FROM (SELECT COUNT(*) [COUNT], groupCol, startOrd, SUM(enrolled) [EnrolledSum] FROM enrolledTest
GROUP BY [groupCol], [startOrd] ) [SourceSelect]
- James
--
James Moore
Red Gate Software Ltd
January 25, 2007 at 6:19 am
Keep it simple. Just use MAX
SELECT COUNT(*) AS "COUNT"
, CAST(StartTimeOrdinal AS INT)
, CAST(PerformingDayOrdinal AS INT)
, MAX(Enrolled) AS Enrolled
FROM OpsSchedule WHERE PerformingDayOrdinal IS NOT NULL
GROUP BY StartTimeOrdinal, PerformingDayOrdinal
ORDER BY PerformingDayOrdinal, StartTimeOrdinal
If even one record is Enrolled (Assuming the col is a bit or 'Y'/'N'), the max will return what you are looking for.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply