Quiero contar distinto de una columna pero con 2 criterios diferentes.
Quiero filtrar todos los correos electrónicos que no contienen yopmail en count_1 y filtrar todos los correos electrónicos que no contienen gmail en count_2 .
Probé este SQL pero no tengo idea de cómo filtrar count_2 . Mi código está filtrando tanto count_1 como count_2 .
SELECT "School"."name" AS "School", count(distinct "public"."users"."id") AS "count_1", count(distinct "public"."users"."id") AS "count_2" FROM "public"."users" LEFT JOIN "public"."user_roles" "User Roles" ON "public"."users"."id" = "User Roles"."user_id" LEFT JOIN "public"."roles" "Role" ON "User Roles"."role_id" = "Role"."id" LEFT JOIN "public"."schools" "School" ON "User Roles"."school_id" = "School"."id" WHERE ("Role"."name" = 'Student' AND "public"."users"."deleted_at" IS NULL AND "public"."users"."activated_at" IS NOT NULL AND NOT (lower("public"."users"."email") like '%yopmail%')) GROUP BY "School"."name" ORDER BY "School"."name" ASC El resultado es así: está filtrando ambos count pero quiero tener valores diferentes de count_1 y count_2 .
School | Count_1 | Count_2 | +------------+-----------+-----------+ | A | 11 | 11 | | B | 20 | 20 | | C | 34 | 34 | +------------+-----------+-----------+Puede lograrlo con una agregación filtrada:
SELECT "School"."name" AS "School", count(distinct "public"."users"."id") AS "count_1", -- the following only counts users where the email column does not contain the value gmail count(distinct users.id) filter (where email not like '%gmail%') AS "count_2" FROM "public"."users" LEFT JOIN "public"."user_roles" "User Roles" ON "public"."users"."id" = "User Roles"."user_id" LEFT JOIN "public"."roles" "Role" ON "User Roles"."role_id" = "Role"."id" LEFT JOIN "public"."schools" "School" ON "User Roles"."school_id" = "School"."id" WHERE ("Role"."name" = 'Student' AND "public"."users"."deleted_at" IS NULL AND "public"."users"."activated_at" IS NOT NULL AND NOT (lower("public"."users"."email") like '%yopmail%')) GROUP BY "School"."name" ORDER BY "School"."name" ASCEl método clásico es usar la expresión CASE.
SELECT "School"."name" AS "School", count(distinct CASE WHEN NOT (lower("public"."users"."email") like '%yopmail%') THEN "public"."users"."id" else NULL END) AS "count_1", count(distinct CASE WHEN NOT (lower("public"."users"."email") like '%gmail%') THEN "public"."users"."id" else NULL END) AS "count_2" FROM "public"."users" LEFT JOIN "public"."user_roles" "User Roles" ON "public"."users"."id" = "User Roles"."user_id" LEFT JOIN "public"."roles" "Role" ON "User Roles"."role_id" = "Role"."id" LEFT JOIN "public"."schools" "School" ON "User Roles"."school_id" = "School"."id" WHERE ("Role"."name" = 'Student' AND "public"."users"."deleted_at" IS NULL AND "public"."users"."activated_at" IS NOT NULL) GROUP BY "School"."name" ORDER BY "School"."name" ASCSi usa una cláusula de filtro, aplíquela a count_1 y count_2, y elimine la condición de correo electrónico de la cláusula WHERE.
SELECT "School"."name" AS "School", count(distinct "public"."users"."id") filter (where NOT (lower("public"."users"."email") like '%yopmail%')) AS "count_1", count(distinct "public"."users"."id") filter (where NOT (lower("public"."users"."email") like '%gmail%')) AS "count_2" FROM "public"."users" LEFT JOIN "public"."user_roles" "User Roles" ON "public"."users"."id" = "User Roles"."user_id" LEFT JOIN "public"."roles" "Role" ON "User Roles"."role_id" = "Role"."id" LEFT JOIN "public"."schools" "School" ON "User Roles"."school_id" = "School"."id" WHERE ("Role"."name" = 'Student' AND "public"."users"."deleted_at" IS NULL AND "public"."users"."activated_at" IS NOT NULL) GROUP BY "School"."name" ORDER BY "School"."name" ASCVer a continuación: violín SQL