February 25, 2007 at 1:43 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 carry that coursegroup?', then resut as below:-
coursegroup | NoOfIPTS
----------------------------
Microsoft | 2
MySQL AB | 1
Oracle Corp | 1
please help me.
February 25, 2007 at 2:53 am
Please provide DDL and sample data and do not provide narrative and listings. http://www.aspfaq.com/etiquette.asp?id=5006
The real problem is that the table are not normalized. In this case, the "CourceGroup" is dependent on coursename and not the other columns of the table.
The solution is to first normalize the data and then the query becomes easy:
select coursegroup, count(*)
from (
select distinct Coursename, coursegroup
from cource
) as Cources
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply