Advice? - 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)

Advice?

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-09 : 09:03:05

Hi,

This is more of a advice request than query help. I thought would be easier to show some data then explain it too. I have the following:

Student_id, subject
001, Maths
001, English
001, Science
002, Maths
002, English
002, Science
002, History
003, English
003, Maths
003, Science
003, RE
004, Science
004, Maths
004, English
004, History
004, French

Aim is to get the top 3 combined courses, for example from the list we can see; English, Maths and Science are the 3 favoured and theres 3 students doing that conbination.

So showing the top 3 course combination with a count.

Any ideas?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-09 : 09:14:04

Could be this:

select s1.subject, s2.subject, s3.subject, count(*)
from table s1
inner join table s2
    on s1.Stundent_id = s2.Student_id
    and s1.subject < s2.subject
inner join table s3
    on s2.Stundent_id = s3.Student_id
    and s2.subject < s3.subject
group by s1.subject, s2.subject, s3.subject
order by count(*) desc

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-09 : 09:16:26

Sorry could you explain this me? Im not actually after query help, its more of a datawarehousing question.

How should I transform the data and play around with it to achieve that goal above. The data is gonna be in chunks and loads of different courses im just looking for the most popular 3 choices, and a count by it?

quote:

Originally posted by bjoerns

Could be this:

select s1.subject, s2.subject, s3.subject, count(*)
from table s1
inner join table s2
    on s1.Stundent_id = s2.Student_id
    and s1.subject < s2.subject
inner join table s3
    on s2.Stundent_id = s3.Student_id
    and s2.subject < s3.subject
group by s1.subject, s2.subject, s3.subject
order by count(*) desc

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-09 : 09:40:15

Well the query goes for every 3 combined distinct courses that belong to the same student, groups by the combination and counts them afterwards. Is this not what you wanted?

I am not an datawarehouse expert. You want to denormalise that nice table?

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-09 : 09:47:49

Ok I understand the query above now, also sometimes some of the students will be studying 5, 6 subjects but they are also doing the '3 popular courses'. Will that take into account?

I can denormalise the table fine, was just after an idea to how get around it, because im all out lol.

The objective is to find out of everyone which are the 3 most picked subjects out of all the choices and combinations the students studying more than 3 subjects have. Hope that makes it clearer.

quote:
Originally posted by bjoerns

Well the query goes for every 3 combined distinct courses that belong to the same student, groups by the combination and counts them afterwards. Is this not what you wanted?

I am not an datawarehouse expert. You want to denormalise that nice table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-09 : 10:14:50

[code]DECLARE @Sample TABLE
(
StudentID INT,
Class VARCHAR(20)
)

INSERT @Sample
SELECT 1, 'Maths' UNION ALL
SELECT 1, 'English' UNION ALL
SELECT 1, 'Science' UNION ALL
SELECT 2, 'Maths' UNION ALL
SELECT 2, 'English' UNION ALL
SELECT 2, 'Science' UNION ALL
SELECT 2, 'History' UNION ALL
SELECT 3, 'English' UNION ALL
SELECT 3, 'Maths' UNION ALL
SELECT 3, 'Science' UNION ALL
SELECT 3, 'RE' UNION ALL
SELECT 4, 'Science' UNION ALL
SELECT 4, 'Maths' UNION ALL
SELECT 4, 'English' UNION ALL
SELECT 4, 'History' UNION ALL
SELECT 4, 'French'

;WITH Yak (ClassName, ClassPath, Combinations)
AS (
SELECT Class,
CAST(Class AS VARCHAR(MAX)),
CAST(1 AS INT)
FROM @Sample
GROUP BY Class

UNION ALL

SELECT s.Class,
y.ClassPath + '-' + s.Class,
y.Combinations + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Class > y.ClassName
WHERE '-' + y.ClassPath + '-' NOT LIKE '%-' + s.Class + '-%'
)

SELECT y.ClassPath,
y.Combinations,
COUNT(f.Classes) AS Students
FROM (
SELECT ClassPath,
Combinations
FROM Yak
GROUP BY ClassPath,
Combinations
) AS y
LEFT JOIN (
SELECT STUFF(t.c, 1, 1, '') AS Classes
FROM (
SELECT StudentID
FROM @Sample
GROUP BY StudentID
) AS s
CROSS APPLY (
SELECT TOP 100 PERCENT
'-' + x.Class
FROM @Sample AS x
WHERE x.StudentID = s.StudentID
GROUP BY x.Class
ORDER BY x.Class
FOR XML PATH('')
) AS t(c)
) AS f ON f.Classes LIKE '%' + y.ClassPath + '%'
GROUP BY y.ClassPath,
y.Combinations
ORDER BY y.Combinations DESC,
COUNT(f.Classes) DESC,
y.ClassPath
OPTION (MAXRECURSION 0)[/code]

E 12°55'05.63"
N 56°04'39.26"

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-09 : 10:14:56

Here some running code:

create table #Temp(
	Student_id int,
	subject varchar(20))

insert into #Temp
select 1, 'Maths' union all
select 1, 'English' union all
select 1, 'Science' union all
select 2, 'Maths' union all
select 2, 'English' union all
select 2, 'Science' union all
select 2, 'History' union all
select 3, 'English' union all
select 3, 'Maths' union all
select 3, 'Science' union all
select 3, 'RE' union all
select 4, 'Science' union all
select 4, 'Maths' union all
select 4, 'English' union all
select 4, 'History' union all
select 4, 'French'

select s1.subject, s2.subject, s3.subject, count(*)
from #Temp s1
inner join #Temp s2
    on s1.Student_id = s2.Student_id
    and s1.subject < s2.subject
inner join #Temp s3
    on s2.Student_id = s3.Student_id
    and s2.subject < s3.subject
group by s1.subject, s2.subject, s3.subject
order by count(*) desc

/* Output
English Maths Science 4
English History Maths 2
English History Science 2
History Maths Science 2
Maths RE Science 1
English Maths RE 1
English French History 1
English French Maths 1
English French Science 1
English RE Science 1
French History Maths 1
French History Science 1
French Maths Science 1
*/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2008-12-09 : 11:28:13

Thanks that seemed to have worked.

I wouldnt get results like

Maths, English, Science OR
Science, English, Maths, etc? itll all just be one combination.

quote:

Originally posted by bjoerns

Here some running code:

create table #Temp(
	Student_id int,
	subject varchar(20))

insert into #Temp
select 1, 'Maths' union all
select 1, 'English' union all
select 1, 'Science' union all
select 2, 'Maths' union all
select 2, 'English' union all
select 2, 'Science' union all
select 2, 'History' union all
select 3, 'English' union all
select 3, 'Maths' union all
select 3, 'Science' union all
select 3, 'RE' union all
select 4, 'Science' union all
select 4, 'Maths' union all
select 4, 'English' union all
select 4, 'History' union all
select 4, 'French'

select s1.subject, s2.subject, s3.subject, count(*)
from #Temp s1
inner join #Temp s2
    on s1.Student_id = s2.Student_id
    and s1.subject < s2.subject
inner join #Temp s3
    on s2.Student_id = s3.Student_id
    and s2.subject < s3.subject
group by s1.subject, s2.subject, s3.subject
order by count(*) desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources