CODEWITHSUNDEEP
sql
zzx
zzx

Reputation: 171

How to write a query in SQL?

I have created some tables in SQL already, they are below:

CREATE TABLE Courses(
    CourseNo INTEGER CHECK(CourseNo>=100 AND CourseNo<=999) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    )

CREATE TABLE CourseSections(
    CourseNo INTEGER CHECK(CourseNo>=100 AND CourseNo<=999),
    SectionNo INTEGER,
    InstructorNo INTEGER NOT NULL,
    Year INTEGER,
    Semester INTEGER,
    RoomNo INTEGER NOT NULL,
    Weekday INTEGER NOT NULL,
    StartTime INTEGER NOT NULL,
    FinishTime INTEGER NOT NULL,
    Capacity INTEGER NOT NULL,
    PRIMARY KEY(CourseNo, SectionNo, Year, Semester)
    )

CREATE TABLE Instructor(
    InstructorNo INTEGER PRIMARY KEY,
    FirstName VARCHAR(40) NOT NULL,
    LastName VARCHAR(40) NOT NULL
    )

CREATE TABLE Students(
    StudentNo INTEGER PRIMARY KEY,
    FirstName VARCHAR(40) NOT NULL,
    LastName VARCHAR(40) NOT NULL,
    Year INTEGER,
    GPA REAL
    )

CREATE TABLE Enrollments(
    CourseNo INTEGER CHECK(CourseNo>=100 AND CourseNo<=999),
    Year INTEGER,
    Semester INTEGER,
    SectionNo INTEGER,
    StudentNo INTEGER,
    Grade REAL,
    PRIMARY KEY(CourseNo, Year, Semester, SectionNo, StudentNo)
    )

CREATE TABLE Areas(
    AreaName VARCHAR(40) PRIMARY KEY
    )

CREATE TABLE AreasOfCourse(
    CourseNo INTEGER CHECK(CourseNo>=100 AND CourseNo<=999),
    AreaName VARCHAR(40),
    PRIMARY KEY(CourseNo, AreaName)
    )

CREATE TABLE AreasOfInstructor(
    InstructorNo INTEGER,
    AreaName VARCHAR(40),
    PRIMARY KEY(InstructorNo, AreaName)
    )

Now, I wanna find the title of each course, and the total enrollment of that course for each semester, I have to write a query for this. So my work is below:

SELECT c1.Title, COUNT(e1.SectionNo), e1.Semester
FROM Courses c1, Courses c2, Enrollments e1, Enrollments e2
WHERE c1.CourseNo = c2.CourseNo AND e1.Semester = e2.Semester AND 
                  e1.SectionNo <> e2.SectionNo

There is an error comes up when I execute this query. The error is:

Column 'Courses.Title` is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone help me figure out how to solve this problem please?

Upvotes: 1

Views: 368

Answers (5)

stinaq
stinaq

Reputation: 1284

The function COUNT() counts the number of tupels/rows and display the result. If you want to count all the rows in your result, that would give you a single value result and that is not compatible with the rest of your result. Where would it put the single result, repeat it for each of the rows?

So therefor you need to add a GROUP BY clause. By doing that you group together a number of tuples and then it can count them by each group and display the single result of each group count.

SELECT c1.Title, COUNT(e1.SectionNo), e1.Semester
FROM Courses c1, Courses c2, Enrollments e1, Enrollments e2
WHERE c1.CourseNo = c2.CourseNo 
    AND e1.Semester = e2.Semester 
    AND e1.SectionNo <> e2.SectionNo
GROUP BY c1.Title, e1.Semester

As the error message said, count is an aggregate function, only displays 1 result, can't be combined with a SELECT that has multiple tuples.

Upvotes: 1

LSerni
LSerni

Reputation: 57398

SELECT c1.Title, COUNT(e1.SectionNo), e1.Semester
    FROM Courses c1,
         Courses c2,
         Enrollments e1,
         Enrollments e2
     WHERE     c1.CourseNo = c2.CourseNo
           AND e1.Semester = e2.Semester
           AND e1.SectionNo <> e2.SectionNo

There is no guarantee that c1.Title will stay the same with different e1.SectionNo, so the SQL wouldn't know what to answer.

You must group the results by Title (adding a GROUP BY c1.Title to the query), and you will get the count of rows, not the total number of different SectionNo's.

If you want to have the total by Title, SectionNo and Semester, you have to GROUP BY all three columns:

SELECT c1.Title, e1.SectionNo, e1.Semester, COUNT(*) AS total
    FROM Courses c1,
         Courses c2,
         Enrollments e1,
         Enrollments e2
     WHERE     c1.CourseNo = c2.CourseNo
           AND e1.Semester = e2.Semester
           AND e1.SectionNo <> e2.SectionNo
     GROUP BY c1.Title, e1.SectioNo, e1.Semester;

Or if you want the sections by title and semester,

SELECT c1.Title, e1.Semester, COUNT(e1.SectionNo) AS total
    FROM Courses c1,
         Courses c2,
         Enrollments e1,
         Enrollments e2
     WHERE     c1.CourseNo = c2.CourseNo
           AND e1.Semester = e2.Semester
           AND e1.SectionNo <> e2.SectionNo
     GROUP BY c1.Title, e1.Semester;

Upvotes: 0

roman
roman

Reputation: 117380

If I got you right, your query a little overcomplicated

 select
    C.Title,
    E.Semester,
    count(*) as Enrollment
from Courses as C
    left outer join Enrollments as E on E.CourseNo = C.CourseNo
group by
    C.Title,
    E.Semester

http://sqlfiddle.com/#!6/efa4b/1

Upvotes: 1

Majid Laissi
Majid Laissi

Reputation: 19788

Well you need to group by c1.Titleand e1.Semester:

SELECT c1.Title, COUNT(e1.SectionNo), e1.Semester
FROM Courses c1, Courses c2, Enrollments e1, Enrollments e2
WHERE c1.CourseNo = c2.CourseNo 
    AND e1.Semester = e2.Semester 
    AND e1.SectionNo <> e2.SectionNo
GROUP BY c1.Title, e1.Semester

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

The error message actually is pretty straight forward: You are missing a GROUP BY clause

SELECT c1.Title, COUNT(e1.SectionNo), e1.Semester
FROM Courses c1, Courses c2, Enrollments e1, Enrollments e2
WHERE c1.CourseNo = c2.CourseNo AND e1.Semester = e2.Semester AND 
                  e1.SectionNo <> e2.SectionNo
GROUP BY c1.Title, e1.Semester

Upvotes: 0

Related Questions