title: How to feed a sql subselect with an outer select?
body: <p>I want to query for all <code>dates</code> in my database, and then use those dates to feed each date into a subselect. I'm looking for a way to combine them.</p>
  <p>To demonstrate, I created the following example.</p>
  <p>Assume I have a few products that I want to obtain the invocation count on each day.
  Let's for simplicity assume there are only 2 products: A and B.</p>
  <pre><code>CREATE TABLE `products` (
    `id` int NOT NULL,
    `product_name` varchar(1) NOT NULL,
    `invocation` date NOT NULL
  );
  
  INSERT INTO `products` (`id`, `product_name`, `invocation`) VALUES
  (1, 'A', '2021-09-08'),
  (2, 'A', '2021-09-08'),
  (3, 'B', '2021-09-07'),
  (4, 'B', '2021-09-08');
  </code></pre>
  <p>I would therefore first have to find out which days are contained in the database at all:</p>
  <pre><code>select invocation from products group by invocation order by invocation desc;
  </code></pre>
  <p>Then, taking these days, I'd have to send a query for <strong>each</strong> day as follows to get my result:</p>
  <pre><code>select invocation,
  count(*) as total,
  (select count(*) from products p where p.invocation = ? and p.product_name = 'A') as count_a,
  (select count(*) from products p where p.invocation = ? and p.product_name = 'B') as count_b
  from products
  where invocation = ?
  group by invocation;
  </code></pre>
  <p>Question: how can I combine those two sqls into one single query?</p>
  <p>Desired result:</p>
  <pre><code>invocation;total;count_a;count_b
  2021-09-07;1;0;1
  2021-09-08;3;2;1
  </code></pre>
  <p>Eg make some kind of <em>outer</em> select for the dates, and then feed the dates into a subselect?</p>
  <hr />
  <p>Update what I tried without the <code>WHERE</code> clause:</p>
  <pre><code>select invocation,
  count(*) as total,
  (select count(*) from products p where p.product_name = 'A') as count_a,
  (select count(*) from products p where p.product_name = 'B') as count_b
  from products
  group by invocation
  order by invocation desc;
  
  invocation;total;count_a;count_b
  2021-09-08;3;2;2
  2021-09-07;1;2;2
  </code></pre>
  <p>The result is WRONG here!</p>