Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Help in grouping Expand / Collapse
Author
Message
Posted Tuesday, October 01, 2013 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
Hello,

SQL Server Version 2000

create table students
(
course_id varchar(5),
course_name varchar(10),
roll_no varchar(3),
student_name varchar(40),
geog_marks varchar(2),
math_marks varchar(2)
)

insert into students values ('1','course1','1','abc','40','')
insert into students values ('1','course1','2','aaa','40','')
insert into students values ('1','course1','3','bbb','40','')

insert into students values ('2','course2','1','abc','','50')
insert into students values ('2','course2','2','aaa','','50')
insert into students values ('2','course2','3','bbb','45','')

insert into students values ('3','course3','1','abc','','50')

insert into students values ('4','course4','1','abc','45','50')
insert into students values ('4','course4','2','aaa','45','50')
insert into students values ('4','course4','3','bbb','45','50')

insert into students values ('5','course5','1','abc','55','35')
insert into students values ('5','course5','2','aaa','45','35')

Above is sample of rows in my table (actually it is not a table, the output of above select * from students is the output of my one query). I need below output please :

course_id	course_name	rollno	description	geog	math
1 course1 1 Default 40

2 course2 1 Default 50
2 course2 3 bbb 45

3 course3 1 abc 50

4 course4 1 Default 45 50

5 course5 1 abc 55 35
5 course5 2 aaa 45 35

Logic behind required output :

1.In course_id 1, all student got 40 marks in geog and no marks in maths, so output should be like above.

2.In course_id 2, all student except rollno 3 got 50 marks in maths and rollno 3 got 45 (not equal to 50), so for all 2 student there should be "Default" word in description column and for rollno 3, his name and marks in geog subject.

3.In course_id 3, there is only one student, so no grouping, so simple show of all the info as above.

4.In course_id 4, all student got same marks in both the subjects, so it will be treated as course_id 1 above.

5.In course_id 5, two student got different marks in both the subjects, so there are no rows greater than 1 for whom we can use "Default" word, so output should be like above.

"Default" word will be used only for those rows in which there are more than one row which have same marks in both or any one subject in a course.

In the continuation of above output, I wish to know in which courses there are only "Default" marks got by students and "Non-Default" marks got by student something like this seperately :

List of Courses in which student got Default marks:
course_id	course_name	rollno	description	geog	math
1 course1 1 Default 40
4 course4 1 Default 45 50
3 course3 1 abc 50 <- Since only one student, so it can be treated as default

And,

List of Courses in which student got Non-Default marks:
course_id	course_name	rollno	description	geog	math
2 course2 1 Default 50
2 course2 3 bbb 45
5 course5 1 abc 55 35
5 course5 2 aaa 45 35

I am using SQL Server 2000, this is big problem with me, if I were using version 2005 and or later one, then it could be solved in easily with over...(partition by) clause, but since we can not move to the higher version, so kindly help me how do I write SQL to get above output.

Kindly let me know, if I am unclear in my question and/or provide more clarification for the required output. Generally, when we see a question related to rollno, course, school name etc. it is treated as a class work, but above is real time data in our corporate school database, so please help me.

For the solution, I have got plenty of examples but all are using version 2005 or greater than, but I am using version 2000, so it forced me write question here.

Thanks and Regards
Girish Sharma
Post #1500654
Posted Wednesday, October 02, 2013 1:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
I am able to get 1st and 2nd output from below query :

For 1st output :

select
a.course_id
,a.[Course]
,a.[Rollno]
,a.Description
,a.[Geog Marks]
,a.[Math Marks]
from
(
select
course_id
,min(course_name) as 'Course'
,min(roll_no) as 'Rollno'
,case when count(course_id)>1 then min('Default') else min(student_name) end as Description
,geog_marks as 'Geog Marks'
,math_marks as 'Math Marks'
from students
group by course_id,geog_marks,math_marks
) a
group by
a.course_id
,a.[Course]
,a.[Rollno]
,a.Description
,a.[Geog Marks]
,a.[Math Marks]

and for 2nd output :

select
a.course_id
,min(a.course) as 'Course'
,min(a.rollno) as 'Rollno'
,min(a.description) as 'Description'
,min(a.[Geog Marks]) as 'Geog Marks'
,min(a.[Math Marks]) as 'Math Marks'
from
(
select
course_id
,min(course_name) as 'Course'
,min(roll_no) as 'Rollno'
,case when count(course_id)>1 then min('Default') else min(student_name) end as Description
,min(student_name) as 'Student Name'
,min(geog_marks) as 'Geog Marks'
,min(math_marks) as 'Math Marks'
from students
group by course_id,geog_marks,math_marks
) a
group by a.course_id
having count(a.course_id)=1

For 3rd,
but I am not able to fetch only course 2 and 5's rows from 1st query. Kindly help me how do I get 3rd output.

Regards
Girish Sharma
Post #1500672
Posted Wednesday, October 02, 2013 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
Yes, now I am able to get 3rd output by using Whole query output Except 2nd query output :

select
a.course_id
,a.[Course]
,a.[Rollno]
,a.Description
,a.[Geog Marks]
,a.[Math Marks]
from
(
select
course_id
,min(course_name) as 'Course'
,min(roll_no) as 'Rollno'
,case when count(course_id)>1 then min('Default') else min(student_name) end as Description
,geog_marks as 'Geog Marks'
,math_marks as 'Math Marks'
from students
group by course_id,geog_marks,math_marks
) a
group by
a.course_id
,a.[Course]
,a.[Rollno]
,a.Description
,a.[Geog Marks]
,a.[Math Marks]
except
select
a.course_id
,min(a.course) as 'Course'
,min(a.rollno) as 'Rollno'
,min(a.description) as 'Description'
,min(a.[Geog Marks]) as 'Geog Marks'
,min(a.[Math Marks]) as 'Math Marks'
from
(
select
course_id
,min(course_name) as 'Course'
,min(roll_no) as 'Rollno'
,case when count(course_id)>1 then min('Default') else min(student_name) end as Description
,min(student_name) as 'Student Name'
,min(geog_marks) as 'Geog Marks'
,min(math_marks) as 'Math Marks'
from students
group by course_id,geog_marks,math_marks
) a
group by a.course_id
having count(a.course_id)=1

Query and output is fine, but I am sure it can be done more efficiently.

Regards
Girish Sharma
Post #1500682
Posted Wednesday, October 02, 2013 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
SELECT 
s.course_id, s.course_name,
roll_no = MIN(s.roll_no),
student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END,
s.geog_marks, s.math_marks
FROM #students s
INNER JOIN (
SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*)
FROM #students
GROUP BY course_id, geog_marks, math_marks
) g
ON g.course_id = s.course_id
AND g.geog_marks = s.geog_marks
AND g.math_marks = s.math_marks
GROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSize
ORDER BY s.course_id, roll_no



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1500693
Posted Wednesday, October 02, 2013 3:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
Hi Chris,

Thanks for your reply. But I am getting :
Invalid object name '#students'.

and what is g.SetSize please?

Regards
Girish Sharma
Post #1500703
Posted Wednesday, October 02, 2013 4:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
#students is a local temporary table I used instead of the permanent sample table students. Just remove the #.

g.SetSize is the calculated row count of the set in the partition (the GROUP BY group).


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1500711
Posted Wednesday, October 02, 2013 4:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
Thanks Chris. This is giving me output of 1st query. Can you please help me how do I get rest 2nd and 3rd query outputs efficiently. Even though I have posted mine way, but I am sure they are not efficient and will work slow if there are thousand of rows in the table.

Regards
Girish Sharma
Post #1500715
Posted Wednesday, October 02, 2013 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
The results of my query match your desired output table. What do you want me to do?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1500720
Posted Wednesday, October 02, 2013 4:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 01, 2014 2:28 AM
Points: 47, Visits: 110
Yes, result of your query match with 1st query output. In my original question, I have posted two more required output based upon output of 1st query. I am just following to get it efficiently (for next two queries) please.

Regards
Girish Sharma
Post #1500723
Posted Wednesday, October 02, 2013 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
-- like this 
SELECT
s.course_id, s.course_name,
roll_no = MIN(s.roll_no),
student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END,
s.geog_marks, s.math_marks
FROM #students s
INNER JOIN (
SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*)
FROM #students
GROUP BY course_id, geog_marks, math_marks
) g
ON g.course_id = s.course_id
AND g.geog_marks = s.geog_marks
AND g.math_marks = s.math_marks
GROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSize
HAVING (CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END) = 'Default'
ORDER BY s.course_id, roll_no

-- or run the result set into a temporary table:
IF object_id('TempDB..#Results') IS NOT NULL
DROP TABLE #Results

SELECT
s.course_id, s.course_name,
roll_no = MIN(s.roll_no),
student_name = CASE WHEN g.SetSize = 1 THEN MIN(s.student_name) ELSE 'Default' END,
s.geog_marks, s.math_marks
INTO #Results
FROM #students s
INNER JOIN (
SELECT course_id, geog_marks, math_marks, SetSize = COUNT(*)
FROM #students
GROUP BY course_id, geog_marks, math_marks
) g
ON g.course_id = s.course_id
AND g.geog_marks = s.geog_marks
AND g.math_marks = s.math_marks
GROUP BY s.course_id, s.course_name, s.geog_marks, s.math_marks, g.SetSize
ORDER BY s.course_id, roll_no

SELECT *
FROM #Results

SELECT *
FROM #Results
WHERE student_name = 'Default'

SELECT *
FROM #Results
WHERE student_name <> 'Default'



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1500736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse