Selection of rows providing a specific condition in sql

I want to create a query like this:
For student_name, if the number of grade=0 and grade=1 students is equal, let’s not select this person, but if the number of grade=0 and grade=1 students is different, let’s select this person. In the query I will use for my example, Jack will not be selected, everyone else will be selected.

CREATE TABLE student
(
student_name VARCHAR(50),
grade CHAR(1)
)

INSERT INTO student
SELECT 'Jack', '0' UNION ALL
SELECT 'Jack', '0' UNION ALL
SELECT 'Jack', '0' UNION ALL
SELECT 'Jack', '1' UNION ALL
SELECT 'Jack', '1' UNION ALL
SELECT 'Jack', '1' UNION ALL
SELECT 'Rose', '0' UNION ALL
SELECT 'Rose', '0' UNION ALL
SELECT 'John', '1' UNION ALL
SELECT 'John', '1' UNION ALL
SELECT 'John', '1' UNION ALL
SELECT 'John', '1' UNION ALL
SELECT 'Dave', '1' UNION ALL
SELECT 'Dave', '1' UNION ALL
SELECT 'Chris', '0'

select * from student

>Solution :

Use aggregation and set the condition in the HAVING clause.

For SQLite:

SELECT student_name
FROM student
GROUP BY student_name
HAVING SUM(grade = 0) <> SUM(grade = 1);

See the demo.

For SQL Server change the HAVING clause to:

HAVING COUNT(CASE WHEN grade = '0' THEN 1 END) <> COUNT(CASE WHEN grade = '1' THEN 1 END);

See the demo.

Leave a Reply