Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-21 : 04:53:47

Hi,

I have 2 tables called Hasama and Student
CREATE TABLE [Hasama] (
[HID] [int] IDENTITY (1, 1) NOT NULL ,
[SID] [int] NOT NULL ,
[hemshech] [smallint] NULL ,
[hasama] [smallint] NULL ,
[hagdara] [smallint] NULL ,
[yetzia_sibot] [varchar] (100) COLLATE Hebrew_CI_AS NULL ,
[shikulim] [varchar] (50) COLLATE Hebrew_CI_AS NULL ,
[hachlata] [tinyint] NULL ,
[entryValid] [tinyint] NOT NULL CONSTRAINT [DF__Hasama__entryVal__345F90D4] DEFAULT (1),
CONSTRAINT [PK_Hasama] PRIMARY KEY CLUSTERED
(
[HID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Hasama_Mosad] FOREIGN KEY
(
[MID]
) REFERENCES [Mosad] (
[SemelMosad]
),
CONSTRAINT [FK_Hasama_Student] FOREIGN KEY
(
[SID]
) REFERENCES [Student] (
[SID]
),
CONSTRAINT [FK_Hasama_tblClass] FOREIGN KEY
(
[HClass]
) REFERENCES [tblClass] (
[ClassCode]
),
CONSTRAINT [FK_Hasama_tblDDLBvalue] FOREIGN KEY
(
[hemshech]
) REFERENCES [tblDDLBvalue] (
[ValueCode]
),
CONSTRAINT [FK_Hasama_tblDDLBvalue1] FOREIGN KEY
(
[hasama]
) REFERENCES [tblDDLBvalue] (
[ValueCode]
),
CONSTRAINT [FK_Hasama_tblDDLBvalue2] FOREIGN KEY
(
[hasamaGuf]
) REFERENCES [tblDDLBvalue] (
[ValueCode]
),
CONSTRAINT [FK_Hasama_tblDDLBvalue3] FOREIGN KEY
(
[hagdara]
) REFERENCES [tblDDLBvalue] (
[ValueCode]
),
CONSTRAINT [FK_Hasama_tblHasama] FOREIGN KEY
(
[hachlata]
) REFERENCES [tblHasama] (
[hasamaCode]
),
CONSTRAINT [FK_Hasama_tblHasamaReason] FOREIGN KEY
(
[hachlata_sibot]
) REFERENCES [tblHasamaReason] (
[HReasonCode]
),
CONSTRAINT [FK_Hasama_tblMisgeret] FOREIGN KEY
(
[misgeret]
) REFERENCES [tblMisgeret] (
[MisgeretCode]
),
CONSTRAINT [FK_Hasama_tblYears] FOREIGN KEY
(
[HYear]
) REFERENCES [tblYears] (
[YearCode]
)
) ON [PRIMARY]
GO

--------------------------------
CREATE TABLE [Student] (
[SID] [int] IDENTITY (1, 1) NOT NULL ,
[SFirstName] [varchar] (50) COLLATE Hebrew_CI_AS NOT NULL ,
[SFamilyName] [varchar] (50) COLLATE Hebrew_CI_AS NOT NULL ,
[STZ] [numeric](10, 0) NOT NULL ,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[SID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_Student] UNIQUE NONCLUSTERED
(
[STZ],
[Spassport],
[SOrigin]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_Student_Mosad] FOREIGN KEY
(
[SMosad]
) REFERENCES [Mosad] (
[SemelMosad]
),
CONSTRAINT [FK_Student_Mosad1] FOREIGN KEY
(
[OwnMosad]
) REFERENCES [Mosad] (
[SemelMosad]
),
CONSTRAINT [FK_Student_Mosad2] FOREIGN KEY
(
[DesMosad]
) REFERENCES [Mosad] (
[SemelMosad]
)
) ON [PRIMARY]
GO

Hasama table can have more than one entry per student so I need it to return only the latest entry.

There is a column called shikulim that can take the values
120
121
122
123

Shikulim can have more than one number in that field such as 120,121.
It can have any combination of the values.

What I need is to return how many students per combination.
For example:
120: 3 students
121: 5 students
120,121, 5 students
120,122: 2 students
120,122,123: 2 students

If a student has the following entry 120,121
then i don't want him to be counted in the 120 group only in the 120,121 group.

Is it possible to do that?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-06-21 : 07:10:58

I think the answer is a simple select with group by clause:
select count(ls.sid)as sid,shikulim
from STUDENT ls
JOIN (Select Max(hID) as mHID, sid FROM Hasama
WHERE (hachlata=0) group by sid) as MaxHasama ON ls.SID = maxHasama.SID
JOIN hasama ON hasama.hID = MaxHasama.mHID

where departure is null

group by shikulim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources