Search code examples
sqlsql-servergroup-bycountcase

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:

    SELECT student_name
FROM student
GROUP BY student_name
HAVING COUNT(CASE WHEN grade = '0' THEN 1 END) <> COUNT(CASE WHEN grade = '1' THEN 1 END);

    See the demo.