Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 11g)
SQL Query [message #665187] Thu, 24 August 2017 11:10 Go to next message
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

[Updated on: Fri, 25 August 2017 01:52] by Moderator

Report message to a moderator

Re: SQL Query [message #665189 is a reply to message #665187] Thu, 24 August 2017 14:41 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: SQL Query [message #665193 is a reply to message #665187] Fri, 25 August 2017 01:28 Go to previous messageGo to next message
John Watson
Messages: 7184
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 #665194 is a reply to message #665187] Fri, 25 August 2017 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: SQL Query [message #665388 is a reply to message #665194] Sat, 02 September 2017 05:43 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
I'll give you a clue.
Group by and count

[Updated on: Sat, 02 September 2017 05:43]

Report message to a moderator

Re: SQL Query [message #665399 is a reply to message #665388] Mon, 04 September 2017 02:00 Go to previous message
quirks
Messages: 61
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"
Previous Topic: mysql statement to PL/SQL
Next Topic: the records with common cust_id should not be shown.
Goto Forum:
  


Current Time: Thu Dec 14 20:06:58 CST 2017

Total time taken to generate the page: 0.12920 seconds