Q:

How to perform group aggregation in MySQL, without nested queries?

My table is like this:
CREATE TABLE USER_TRANSACTIONS (
  START_TIME        BIGINT UNSIGNED  NOT NULL,
  APPLICATION_ID    CHAR(64) BINARY  NOT NULL,
  ENTRY_POINT       CHAR(255) BINARY NOT NULL,
  USER_ID           CHAR(64) BINARY  NOT NULL,
  ERROR_VIOLATION   BIT(1) NOT NULL,
  LATENCY_VIOLATION BIT(1) NOT NULL,
  PRIMARY KEY (START_TIME, APPLICATION_ID, ENTRY_POINT, USER_ID)
)

What I'd like to achieve is a summary as follow: per each entry_point I'd like to see how many unique users and how many of them had errors and latency problems.
For example:
ENTRY_POINT | TOTAL_USERS | TOTAL_ERRORS | TOTAL_LATENCY
page1       | 2           | 2            | 1
page2       | 1           | 1            | 1

I can achieve this goal with this query:
SELECT UT.ENTRY_POINT, COUNT(USER_ID) AS TOTAL_USERS, SUM(EXP_ERRORS) AS TOTAL_ERRORS, SUM(EXP_LATENCY) AS TOTAL_LATENCY
FROM (
    SELECT ENTRY_POINT, USER_ID,
    BIT_OR(ERROR_VIOLATION) AS EXP_ERRORS,
    BIT_OR(LATENCY_VIOLATION) AS EXP_LATENCY
    FROM user_transactions
    GROUP BY ENTRY_POINT, USER_ID
) AS UT
GROUP BY UT.ENTRY_POINT;

The nested query is using to summarize if users have experienced errors or latency problems, but on a table with lots of data I have performance issues.
My question is how can I optimize this query to avoid the usage of the inner sub-query?

A:

Use count(distinct).  Here is one way to write the query:
SELECT ENTRY_POINT, COUNT(DISTINCT USER_ID),
       SUM(ERROR_VIOLATION > 0) AS TOTAL_ERRORS,
       SUM(LATENCY_VIOLATION > 0) AS TOTAL_LATENCY
FROM user_transactions
GROUP BY ENTRY_POINT;

If you want users with errors instead of total errors:
SELECT ENTRY_POINT, COUNT(DISTINCT USER_ID),
       COUNT(DISTINCT CASE WHEN ERROR_VIOLATION > 0 THEN USER_ID END) AS TOTAL_ERRORS,
       COUNT(DISTINCT CASE WHEN LATENCY_VIOLATION > 0 THEN USER_ID END) AS TOTAL_LATENCY
FROM user_transactions
GROUP BY ENTRY_POINT;

