Summarize results per ID for different groups

Question

I am looking to summarize my results per ID (in this example just one shown) for each domain.

The idea is to get boolean values (T/F) for the three actions types (which are in a column), and place them in a row for each ID.

However, these results need to be grouped for each domain in the URL column.

Now, I can use CASE WHEN conditions to get the T/F values, however, then I don't get my results in a single row...

To get the domain I think I can use SUBSTRING.

Initial table:

+----+----------+----------------+
| ID | Action   | URL            |
+----+----------+----------------+
| 1  | Click    | www.google.com |
+----+----------+----------------+
| 1  | Hover    | www.google.com |
+----+----------+----------------+
| 1  | Download | www.reddit.com |
+----+----------+----------------+

Desired output:

+----+-------+-------+----------+------------+
| ID | Click | Hover | Download | Domain     |
+----+-------+-------+----------+------------+
| 1  | T     | T     | F        | google.com |
+----+-------+-------+----------+------------+
| 1  | F     | F     | T        | reddit.com |
+----+-------+-------+----------+------------+

Answer 1

Use condition aggregation:

SELECT
    id,
    MAX(CASE WHEN action = 'Click' THEN 'T' ELSE 'F' END) click,
    MAX(CASE WHEN action = 'Hover' THEN 'T' ELSE 'F' END) hover,
    MAX(CASE WHEN action = 'Download' THEN 'T' ELSE 'F' END) download,
    SUBSTR(url, 5) domain
FROM mytable
GROUP BY id, SUBSTR(url, 5)

To generate the domain name, you can simply remove the first 4 characters from the url (hence this assumes that all urls begin with 'www.').

This Demo on DB Fiddle with your sample data returns:

| id  | click | hover | download | domain     |
| --- | ----- | ----- | -------- | ---------- |
| 1   | T     | T     | F        | google.com |
| 1   | F     | F     | T        | reddit.com |