Query Help Needed - 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 2005 Forums

Transact-SQL (2005)

Query Help Needed

Author

Topic

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-04-16 : 08:09:58

Hi

Have a Scenario like this , Kindly use the Script below

CREATE TABLE [dbo].[Tbl_Atp](
[AID] [int] NOT NULL,
[TID] [int] NULL,
[UID] [int] NULL,

CONSTRAINT [PK_Tbl_Atp] PRIMARY KEY CLUSTERED
(
[AID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(1,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(2,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(3,200,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(4,300,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(5,100,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(6,100,1064)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(7,100,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(8,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(9,200,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(10,300,1064)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(11,200,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(12,300,1064)

Uid = UserID
Tid = TestID
Aid = AssessmentID P.K

I need Count of Users againest each TestID who have attempted a given testID more than Once

Ex : TID = 100 has been attempted more than once by two users, so my output would be
Ex : TID = 200 has been attempted more than once by only one user, so my output would be

Tid Count
100 2
200 1
300 1

I tried

SELECT TID , Count(Distinct UID),Count(AID) FROM [Tbl_Atp]
Group By UID,TID
Having Count(AID) > 1

Desired Output

Tid Count
100 2
200 1
300 1

Need your help

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 08:30:23

SELECT TID,COUNT(attempts) as counting FROM
(
SELECT TID , UID,Count(AID) as attempts FROM [Tbl_Atp]
Group By TID,UID
Having Count(distinct AID) > 1
) AS T
GROUP BY TID

Madhivanan

Failing to plan is Planning to fail

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-16 : 08:31:05

Hi,

SELECT TID, COUNT(*) 'Count'
FROM
(SELECT UID, TID
FROM [Tbl_Atp] GROUP BY UID, TID HAVING COUNT(*) >1
)X GROUP BY TID

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-16 : 08:41:36

Hi

CREATE TABLE [dbo].[Tbl_Atp](
[AID] [int] NOT NULL,
[TID] [int] NULL,
[UID] [int] NULL,

CONSTRAINT [PK_Tbl_Atp] PRIMARY KEY CLUSTERED
(
[AID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(1,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(2,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(3,200,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(4,300,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(5,100,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(6,100,1064)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(7,100,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(8,100,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(9,200,1012)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(10,300,1064)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(11,200,1000)
INSERT INTO [Tbl_Atp](AID,TID,UID)VALUES(12,300,1064)

select a.tid,count(a.uid) from
(
select tid,uid,count(aid) aa from [Tbl_Atp]
group by tid,uid
having count(aid)>1
) a
group by tid

Kunal

zubairmasoodi
Starting Member

35 Posts

Posted - 2009-04-16 : 08:46:26

Thanks to you all Gentleman

and Mr madhivanan, AID is Primary key , so i guess we can eliminate Distinct Clause to make it more optimized

Cheers!!

With regards
Zubair Masoodi
(Every day's a school day)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 08:50:05

quote:
Originally posted by zubairmasoodi

Thanks to you all Gentleman

and Mr madhivanan, AID is Primary key , so i guess we can eliminate Distinct Clause to make it more optimized

Cheers!!

With regards
Zubair Masoodi
(Every day's a school day)

Yes it is. Remove Distinct and use

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources