SQL Query [message #665187] |
Thu, 24 August 2017 11:10 |
|
sql_123
Messages: 1 Registered: August 2017
|
Junior Member |
|
|
Experts need help, I am new to this and have tried everything..
I have a student table
STUDENT ID NAME USERNAME Enrolled ALIAS
1 SAM sam_sc Science SAM
1 SAM sam_mt Maths SAM
2 HARRY harry_sc Science HARRY
3 JOHN john_sc Science JOHN
3 JOHN john_mt Maths JOHN
I want to display single record for each student with an additional field 'Multiple Classes' if student is enrolled in multiple classes value 'Yes' else 'No'. The result set should look like below.
STUDENT ID NAME USERNAME ALIAS MULTIPLE CLASSES
1 SAM sam_sc SAM YES
2 HARRY harry_sc HARRY NO
3 JOHN john_mt JOHN YES
|
|
|
|
Re: SQL Query [message #665193 is a reply to message #665187] |
Fri, 25 August 2017 01:28 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This looks like a college homework assignment. If so, you need to show what SQL you have tried so far (you say you have tried "everything"?) including the CREATE TABLE and INSERT statements to set up he problem.
And PLEASE use [code] tags.
|
|
|
|
|
Re: SQL Query [message #665399 is a reply to message #665388] |
Mon, 04 September 2017 02:00 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Because the Username seems to be derived from username and enrolled course they are distinct for each record. Therefore you can not group by this value. Is it necessary to show this value in your final result, and if so, which of the both per student? If not, this is your solution:
WITH
STUDENT_TABLE
AS
(SELECT 1 AS "STUDENT ID", 'SAM' AS NAME, 'sam_sc' AS USERNAME, 'Science' AS ENROLLED, 'SAM' AS ALIAS FROM DUAL
UNION ALL
SELECT 1, 'SAM', 'sam_mt', 'Maths', 'SAM' FROM DUAL
UNION ALL
SELECT 2, 'HARRY', 'harry_sc', 'Science', 'HARRY' FROM DUAL
UNION ALL
SELECT 3, 'JOHN', 'john_sc', 'Science', 'JOHN' FROM DUAL
UNION ALL
SELECT 3, 'JOHN', 'john_mt', 'Maths', 'JOHN' FROM DUAL)
SELECT "STUDENT ID"
,NAME
,ALIAS
,CASE WHEN COUNT(*) > 1 THEN 'YES' ELSE 'NO' END AS "MULTIPLE CLASSES"
FROM STUDENT_TABLE
GROUP BY "STUDENT ID", NAME, ALIAS
ORDER BY "STUDENT ID"
|
|
|