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.
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