Redshift having query with count function not working as expected

Question

This is my query:

SELECT DISTINCT "student_id" 
FROM "school_1__im_student_model"."students"
GROUP BY "student_id"
HAVING COUNT("age") >= '2'

The result is

| "student_id" |
| 1000         |
| 1111         |

I need to get the total count for this query.

SELECT COUNT(DISTINCT "student_id") 
FROM "school_1__im_student_model"."students"
GROUP BY "student_id"
HAVING COUNT("age") >= '2'

But with this query, I get values like this:

|count|
| 1   |
| 1   |

but I need to get total records. ex - 2

Thiago Mata · Accepted Answer · 2022-04-27 00:36:53Z

The "having" command is executed after the COUNT(DISTINCT "student_id"), you should use subqueries to build the result that you want step by step.

Something like



-- create
CREATE TABLE ROOM_STUDENT (
  room_id INTEGER,
  student_id INTEGER,
  age INTEGER
);

-- insert
INSERT INTO ROOM_STUDENT VALUES (1, 10001, 22);
INSERT INTO ROOM_STUDENT VALUES (1, 10002, 23);
INSERT INTO ROOM_STUDENT VALUES (1, 10003, 23);
INSERT INTO ROOM_STUDENT VALUES (2, 10001, 22);
INSERT INTO ROOM_STUDENT VALUES (2, 10002, 23);
INSERT INTO ROOM_STUDENT VALUES (3, 10002, 23);
INSERT INTO ROOM_STUDENT VALUES (3, 10003, 24);

with sub as (
  SELECT student_id, count(*) as count_rooms FROM ROOM_STUDENT WHERE age > 22 group by student_id
)
select count(*) as count_students from sub where count_rooms > 2;

You can see it running here https://onecompiler.com/postgresql/3y28krk5u

Here is a link teaching more about the "with" command in SQL https://modern-sql.com/feature/with#:~:text=Syntax&text=The%20syntax%20after%20the%20keyword,query%E2%80%94again%20in%20parentheses.

Collectives™ on Stack Overflow

Redshift having query with count function not working as expected

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related