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 carry that coursegroup?', then resut as below:-

coursegroup | NoOfIPTS
----------------------------
Microsoft | 2
MySQL AB | 1
Oracle Corp | 1

please help me.

Select coursegroup, count(*) NoOfIPTS
From course INNER JOIN ipts On course.npp = ipts.npp
Group By coursegroup

Cheers,
Prabodh

Since you only want to count each IPTS once, try using the following query:

SELECT CourseGroup, COUNT(NPP) AS NPPCount
FROM (SELECT DISTINCT CourseGroup, NPP
FROM Course) A
GROUP BY CourseGroup

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp