Proper way to count how many times an item from 2 lists of items are ordered together

I am currently self-learning SQL so for most of you this would probably seem like a simple question (if I expressed it correctly).

I have a table ‘orders’ that look like this:

Orddid(Uid)    Ordmid      Odate        Itmsid
-----------    ------      -----        ------
  100101       100101    01.12.2018       12
  100102       100101    01.12.2018       88
  100103       100101    01.12.2018       57
  100104       100102    01.12.2018       12

What I want to do is count the times that any item from 2 lists of items (as in IN (itmsid1, itmsid2) coexists for all Ordmids.

For example, if I query about itemsid in (12,99) and also itemsid in (22,57) I would get a count of 1 at the end.

How do I do that?

EDIT: I have to say that this community is amazing! Lightning fast responses and very supportive even. Thank you very much people. I owe you!

Answer

I interpret your question as:

How many times does an Ormid group feature itemid 12 or 99, in combination with itemid 22 or 57..

Meaning, an ormid group should have either a 12 and a 22, or 12 and 57, or 99 and 22, or 99 and 27 (at least.. 12,22,57 etc would also be permitted). In plain english this might be expressed as “How many times did someone buy (a keyboard or a mouse) in combination with (a memory stick or a printer cartridge)” – to qualify for a special offer, someone has to buy at least one item from group 1, and one item from group 2

Many ways to do, here’s one:

SELECT COUNT(distinct t_or_nn.ormid) FROM

(SELECT ormid FROM orders WHERE itemid in (12,99)) t_or_nn
INNER JOIN
(SELECT ormid FROM orders WHERE itemid in (22,57)) tt_or_fs
ON t_or_nn.ormid = rr_or_fs.ormid

How it works:

Two subqueries; one pulls a list of all the ormids that have a 12 or 99. The other pulls a list of all the ormids that have a 22 or 57.

When these lists are joined, only the ormids that are equal will survive to become a result of the join

We thus end up with a list of only those ormids that have a 12 or 99, in combination with a 22 or 57. Counting this (distinctly, to prevent an ormid with 12,99,22 being counted as 2, or an ormid of 12,22,57,99 items being counted as 4) provides our answer.

If you need more detail on why having an ormid with itemids 12,99,22,57 results in a count of 4, let me know. I won’t launch into talking about cartesian products right away as you might already know..

There are a few ways to solve things like this, I’ve picked on this way as it’s fairly easy to explain because the query logic is fairly well aligned with the way a human might think about it