CODEWITHSUNDEEP
sqlmariadb
NewToPHP
NewToPHP

Reputation: 47

Count on left join querty

Just help me with this query please.

SELECT COUNT (name.IN) AS Total, name.St_Name, name.Class_Id,
    teacher.Class_Id
  FROM name
  Left Join teacher ON  name.Class_Id = teacher.Class_Id
  WHERE name.IN = '1';

Question : how do I COUNT(name.IN) as total and get the number of students that name.Class_Id and teacher.Class_Id are the same.

Upvotes: 0

Views: 46

Answers (2)

LukStorms
LukStorms

Reputation: 29647

If you use an aggregation function like COUNT or SUM or MAX?

Then when you add other fields to the SELECT, then those fields should be in the GROUP BY.

This is true for most databases. But MySql can be an exception to that SQL92 rule.
In MariaDB it's not permitted to omit those fields from the GROUP BY if the ONLY_FULL_GROUP_BY SQL_MODE is used.

So try this:

SELECT 
 COUNT(name.IN) AS Total,
 COUNT(DISTINCT name.St_Name) AS TotalUniqueStudentNames, 
 COUNT(teacher.Class_id) AS TotalMatchingTeacherClass,
 name.Class_Id
FROM name
LEFT JOIN teacher ON name.Class_Id = teacher.Class_id
WHERE name.IN = '1'
GROUP BY name.Class_Id
ORDER BY name.Class_Id

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group

    SELECT (name.IN) AS Total, name.St_Name, name.Class_Id, teacher.Class_Id
        FROM name
        Left Join teacher ON  name.Class_Id = teacher.Class_Id
        WHERE name.IN = '1'
group by  name.St_Name, name.Class_Id, teacher.Class_Id

Upvotes: 1

Related Questions