Can anyone think of a better way to structure a SQL query for this problem?
I want to group by account_id and determine whether a type of row exists in the table and just aggregate that to a simple true/false as to whether that type of row exists for that account_id
TABLE user_account_values
COLUMNS:
user_id,
account_id,
svc_id,
card_id,
name,
status
Account_id is not unique in the table
e.g. result is something like below, where category is determined by a defined list of possible values
| AccountID | Category1Exists | Category2Exists |
|---|---|---|
| 123 | true | false |
| 124 | false | true |
| 125 | false | false |
My current solution is to create a type of bitmap based on whether the row matches the category then aggregate that with MAX() to select 1 as true if the category exists for the account_id
SELECT
account_id,
MAX(first) AS firstExists,
MAX(final) AS finalExists
FROM
(SELECT
account_id,
CASE
WHEN name IN ('something1_first', 'something2_first')
THEN 1
ELSE 0
END AS first,
CASE
WHEN name IN ('something1_final', 'something2_final')
THEN 1
ELSE 0
END AS final
FROM
user_account_values
WHERE
user_id = :user
AND account_id IS NOT NULL
AND status = 'A')
GROUP BY
account_id
Is there a simpler way to achieve this?
I also thought maybe you could have a select account_id where name in (*) for each category and then join them all together on account_id but it seems like it would be worse performance and I may have upwards of 4/5 categories I need to check in the final query.
Combine a conditional within aggregation.
SELECT account_id,
MAX(CASE WHEN (name IN ('something1_first','something2_first')) THEN 'Y' ELSE 'N' END) firstexists,
MAX(CASE WHEN (name IN ('something1_final','something2_final')) THEN 'Y' ELSE 'N' END) finalexists
FROM mytable
GROUP BY account_id
This works because Y collates higher than N so if there’s a single Y coming from that case function the overall result will be Y. There are of course numerous variations on this theme, but this shows the concept.
If you literally want the string 'true' and 'false' (you can’t have real Booleans, at least not as of 19c) you could substitute those for Y and N because true will evaluate higher than false alphabetically. But that’s not good programming because not as self-evident. If you definitely wanted the strings and didn’t want to rely on the accidental ordering between these two strings, then simply translate Y/N (or 0/1 or whatever properly orders) to whatever string you prefer:
SELECT account_id,
DECODE(MAX(CASE WHEN (name IN ('something1_first','something2_first')) THEN 'Y' ELSE 'N' END),'Y','true','false') firstexists,
DECODE(MAX(CASE WHEN (name IN ('something1_final','something2_final')) THEN 'Y' ELSE 'N' END),'Y','true','false') finalexists
FROM mytable
GROUP BY account_id
4