Count Function

Subscribe in a reader

By: rekha singh | 31 Aug 2010 5:20 pm

Hello,

I am a beginner SQL learner. I not sure where to add a count statement in a existing SQL code. The code is below. So I can understand how to do this.

Thank You.

SELECT
sg.StoreCode,
sg.TermID,
sg.Grade,
sg.Course_Name,
s.LastFirst,
s.ID,
FROM
(SELECT s.Student_Number, COUNT(*) as "Number"
FROM Students s
WHERE s.Student_Number > 25000
GROUP BY s.Student_Number)

FROM Students s
INNER JOIN StoredGrades sg
ON s.ID = sg.STUDENTID

WHERE s.Enroll_Status = 0
AND sg.TermID > 1699
AND sg.Storecode IN ('Q1')
AND s.SchoolID = 4
AND (sg.Grade <='65' And sg.Grade <> '100')

ORDER BY sg.Storecode,
s.lastfirst

Comments

Which field do you want to count?

By: rekha singh | 31 Aug 2010

I would like to count the Student Number. What I am try to do is count how many times the student is repeated.

For example is the student shows up twice in the list then the count would be 2.

Thnaks

By: rekha singh | 31 Aug 2010

OK, didn't really read your code.

This likely is not getting all the data you want as I don't have your tables and constraints. Give this a shot and let's hear what is wrong with the results

SELECT COUNT(s.Student_ Number) AS number,
sg.StoreCode,
sg.TermID,
sg.Grade,
sg.Course_Name,
s.LastFirst,
s.ID
FROM Students s
INNER JOIN StoredGrades sg
ON s.ID = sg.STUDENTID
WHERE s.Enroll_Status = 0
AND sg.TermID > 1699
AND sg.Storecode IN ('Q1')
AND s.SchoolID = 4
AND (sg.Grade <='65' And sg.Grade <> '100') --this could be a row
killer in the where
GROUP BY
sg.StoreCode,
sg.TermID,
sg.Grade,
sg.Course_Name,
s.LastFirst,
s.ID
ORDER BY
sg.Storecode,
s.lastfirst

By: rekha singh | 31 Aug 2010

I tried the code. I am using Aqua Data Studio for the query. The Error I get when I run the code below is:

>[Error] Script lines: 1-25 ------------ --------- ----
ORA-00923: FROM keyword not found where expected

Just so you understand the where:

WHERE s.Enroll_Status = 0 --- Checks to see if active student
> AND sg.TermID > 1699 --- Just gets the current Year
> AND sg.Storecode IN ('Q1')--- Just gets this store code
> AND s.SchoolID = 4 --- Gets just one building
> AND (sg.Grade <='65' And sg.Grade <> '100') --this could be a row --- Only find
Grades less than 65. It would pull 100 So I put in the code <>100

The count function goes in the Select statement, does it need to be the first thing in the list?

By: rekha singh | 31 Aug 2010

Oh, this is Oracle. That little bit of information helps. Run this is SQLPlus and shoot back the errors, also delete the -- line as the is an SQL Server comment marker and Oracle will REALLY have no appreciation for it.

By: rekha singh | 31 Aug 2010

Count Function

Comments

Leave a comment