February 25, 2007 at 6:16 am
Hi,
I've these following table,
ipts
npp | name
--------------------------------
A134 | James Computer Centre
A212 | Johnny Tuition
course
courseid | npp | coursename | coursegroup
----------------------------------------------
1 | A134 | Ms Word | Microsoft
2 | A134 | Windows XP | Microsoft
3 | A134 | MySQL 5.0 | MySQL AB
4 | A212 | Windows XP | Microsoft
5 | A212 | Oracle 10g | Oracle Corp
6 | A212 | Oracle Dev | Oracle Corp
How to query - 'how many IPTS offer each of coursegroup?', then resut as below:-
coursegroup | NoOfIPTS
----------------------------
Microsoft | 2 ----> A134, A212
MySQL AB | 1 ----> A134
Oracle Corp | 1 ----> A212
i dont know to query, please help me.
February 25, 2007 at 6:16 pm
You need cross-tab query which SQL does not provide yet. You need to create script as below:
SET NOCOUNT ON
-- Drop tables if alraedy exist
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IPTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IPTS]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TmpCourseGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TmpCourseGroup]
-- Creat table IPTS and populate data
Create Table [dbo].IPTS (npp varchar(50) NULL, [Name] Varchar(100) NULL)
Insert Into [dbo].IPTS
SELECT 'A134', 'James Computer Centre' UNION ALL
SELECT 'A212', 'Johnny Tuition'
-- Creat table Course and populate data
Create Table [dbo].Course (CourseID int identity(1,1), npp varchar(50) NULL, [CourseName] Varchar(100) NULL, CourseGroup Varchar(50) NULL)
Insert Into [dbo].Course
SELECT 'A134','Ms Word','Microsoft' UNION ALL
SELECT 'A134','Windows XP','Microsoft' UNION ALL
SELECT 'A134','MySQL 5.0','MySQL AB' UNION ALL
SELECT 'A212','Windows XP','Microsoft' UNION ALL
SELECT 'A212','Oracle 10g','Oracle Corp' UNION ALL
SELECT 'A212','Oracle Dev','Oracle Corp'
-- Creat table TmpCourseGroup and populate data
Create Table [dbo].TmpCourseGroup (CourseGroup Varchar(50) NULL, npp varchar(50) NULL, [Count] INT NULL)
Insert into TmpCourseGroup
Select CourseGroup,[npp], Count(*) [Count]
From [dbo].Course
Group By CourseGroup,[npp]
Order By CourseGroup,[npp]
Select * From [dbo].TmpCourseGroup
DECLARE @npp Varchar(50)
DECLARE @sql Varchar(5000)
DECLARE @Num INT
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT npp FROM [dbo].TmpCourseGroup
GROUP BY npp
ORDER BY npp
SET @sql = ''
SET @Num = 0
OPEN Fields
FETCH NEXT FROM Fields INTO @npp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1
SET @sql = 'CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)=0 THEN '''' ELSE ''' + @npp + ' '' END
'
ELSE
SET @sql = @sql + '+ CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)=0 THEN '''' ELSE ''' + @npp + ' '' END As [Result]
'
FETCH NEXT FROM Fields INTO @npp
END
CLOSE Fields DEALLOCATE Fields
SET @sql = 'SELECT CourseGroup,' + @sql + '
FROM [dbo].TmpCourseGroup
GROUP BY CourseGroup
ORDER BY CourseGroup
'
--PRINT @sql
EXEC (@SQL)
February 25, 2007 at 6:27 pm
Sorry, I missed count.
Use this script to include count:
SET NOCOUNT ON
-- Drop tables if alraedy exist
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Course]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Course]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IPTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IPTS]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TmpCourseGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TmpCourseGroup]
-- Creat table IPTS and populate data
Create Table [dbo].IPTS (npp varchar(50) NULL, [Name] Varchar(100) NULL)
Insert Into [dbo].IPTS
SELECT 'A134', 'James Computer Centre' UNION ALL
SELECT 'A212', 'Johnny Tuition'
-- Creat table Course and populate data
Create Table [dbo].Course (CourseID int identity(1,1), npp varchar(50) NULL, [CourseName] Varchar(100) NULL, CourseGroup Varchar(50) NULL)
Insert Into [dbo].Course
SELECT 'A134','Ms Word','Microsoft' UNION ALL
SELECT 'A134','Windows XP','Microsoft' UNION ALL
SELECT 'A134','MySQL 5.0','MySQL AB' UNION ALL
SELECT 'A212','Windows XP','Microsoft' UNION ALL
SELECT 'A212','Oracle 10g','Oracle Corp' UNION ALL
SELECT 'A212','Oracle Dev','Oracle Corp'
-- Creat table TmpCourseGroup and populate data
Create Table [dbo].TmpCourseGroup (CourseGroup Varchar(50) NULL, npp varchar(50) NULL, [Count] INT NULL)
Insert into TmpCourseGroup
Select CourseGroup,[npp], Count(*) [Count]
From [dbo].Course
Group By CourseGroup,[npp]
Order By CourseGroup,[npp]
Select * From [dbo].TmpCourseGroup
DECLARE @npp Varchar(50)
DECLARE @sql Varchar(5000)
DECLARE @SQL2 Varchar(5000)
DECLARE @Num INT
DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT npp FROM [dbo].TmpCourseGroup
GROUP BY npp
ORDER BY npp
SET @sql = ''
SET @SQL2 = ''
SET @Num = 0
OPEN Fields
FETCH NEXT FROM Fields INTO @npp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Num = @Num + 1
IF @Num = 1
SET @sql = 'CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)=0 THEN '''' ELSE ''' + @npp + ' '' END
'
ELSE
SET @sql = @sql + '+ CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)=0 THEN '''' ELSE ''' + @npp + ' '' END
'
IF @Num = 1
SET @SQL2 = 'COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)
'
ELSE
SET @SQL2 = @SQL2 + '+ COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),npp),''NA'') = ''' + @npp + ''' THEN [npp] ELSE NULL END)
'
FETCH NEXT FROM Fields INTO @npp
END
CLOSE Fields DEALLOCATE Fields
SET @sql = 'SELECT CourseGroup,' + @SQL2 + ' As [Count],' + @sql + ' As [Result]
FROM [dbo].TmpCourseGroup
GROUP BY CourseGroup
ORDER BY CourseGroup
'
--PRINT @sql
EXEC (@SQL)
February 26, 2007 at 4:08 am
SELECT coursegroup
,COUNT(*) AS NoOfIPTS
FROM (
SELECT DISTINCT coursegroup, nnp
FROM course
) D
GROUP BY coursegroup
February 26, 2007 at 4:31 am
Or
SELECT coursegroup,COUNT(DISTINCT npp) AS [NoOfIPTS]
FROM dbo.Course
GROUP BY coursegroup
Far away is close at hand in the images of elsewhere.
Anon.
February 27, 2007 at 5:40 pm
tq all. i will help me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply