SQL select column group by where the ratio of a value is 1

I am using PSQL.

I have a table with a few columns, one column is event that can have 4 different values – X1, X2, Y1, Y2. I have another column that is the name of the service and I want to group by using this column.

My goal is to make a query that take an event and verify that for a specific service name I have count(X1) == count(X2) if not display a new column with “error”

Is this even possible? I am kinda new to SQL and not sure how to write this.

So far I tried something like this

select 
    service_name, event, count(service_name) 
from 
    service_table st 
group by 
    (service_name, event); 

I am getting the count of each event for specific service_name but I would like to verify that count of event 1 == count of event 2 for each service_name.

I want to add that each service_name have a choice of 2 different event only.

Answer

You may not need a subquery/CTE for this, but it will work (and makes the logic easier to follow):

WITH event_counts_by_service AS (SELECT
service_name
, COUNT(CASE WHEN event='X1' THEN 1 END) AS count_x1
, COUNT(CASE WHEN event='X2' THEN 1 END) AS count_x2
FROM service_table
GROUP BY service_name)
SELECT service_name
, CASE WHEN count_x1=count_x2 THEN NULL ELSE 'Error' END AS are_counts_equal
FROM event_counts_by_service