SQL an ID in one group and an id from another

I have a request where I need a count where a participant completed an activity under one id (the gateway to completing other things) and then an id in a list of other ids. Right now, this code is only coming back with the the a count of the one id but not saying that one id complete + the other ids.

select
    plan,
    count(*)
from
    table
where
    id = 1234
    and
    id in (1354, 5842, 8565)
group by
    plan

How can I get this where it gives me the count of data from id 1234 plus ids in (1354, 5842, 8565), rather than just a count of data from the id 1234

Answer

• It seems you’ve fallen for the “SQL looks like a natural English-language query” trap.
  • Fun fact: it isn’t.
  • You may have heard of COBOL: the business-rules programming language that resembles English-language, but COBOL actually has rigid, inflexible grammatical and semantic rules that betray the friendly-looking syntax.
  • SQL is very much the same: it’s a very, very thin veneer of approachability over a very hostile and academic branch of mathematics called Relational Calculus.
  • I curse the SQL language high-priests every time I have to type-out their decreed unnecessarily verbose and logically inconsistent SELECT e FROM a WHERE b GROUP BY c HAVING d ORDER BY f statements.

ANYWAY!…

Change your query to remove the id = 1234 so it uses only the id IN ( ... ) clause:

select
    plan,
    count(*)
from
    table
where
    id in (1354, 5842, 8565)
group by
    plan

If you want a query that better follows your described mental-model, then use a UNION [ALL] query:

SELECT
    plan,
    COUNT(*) AS "count"
FROM
    table
WHERE
    Id = 1354
GROUP BY
    plan

UNION

SELECT
    plan,
     COUNT(*) AS "count"
FROM
    table
WHERE
    Id in ( 5842, 8565 )
GROUP BY
    plan

---

Explanation:

• The AND operator in SQL represents a Logical Conjunction: that is the left-hand-side and right-hand-side of the AND operator must be true.
  • Given that Id is your primary-key (at least, I hope it is!) it is impossible for any single row in table to have an Id value which is simultaneously equal to 1234 and 5842. That’s why your query is only matching 1 row in total.
    • Also, surprisingly, SQL engines do not warn you that a predicate is logically impossible or otherwise has redundant sub-expressions.
      • My personal opinion is that if a computer language allows for loose syntax it should yield warnings whenever something ain’t right is observed. This is another reason why I dislike SQL.
• x IN ( a, b, c ) is semantically identical to ( x = a OR x = b OR x = c ).
  • The OR is a Logical Disjunction, and not an English-language “or”: https://en.wikipedia.org/wiki/Logical_disjunction
    • This is another gotcha in the fallacy of SQL’s “natural” English-language veneer.
• I assume you thought the Id = 1234 AND Id IN ( a, b, c ) used “and” as a Logical Disjunction, not a Logical Injunction. Hence the fix is to replace the AND with an OR.