How to feed a sql subselect with an outer select?

Question

I want to query for all dates in my database, and then use those dates to feed each date into a subselect. I'm looking for a way to combine them.

To demonstrate, I created the following example.

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.

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');

I would therefore first have to find out which days are contained in the database at all:

select invocation from products group by invocation order by invocation desc;

Then, taking these days, I'd have to send a query for each day as follows to get my result:

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;

Question: how can I combine those two sqls into one single query?

Desired result:

invocation;total;count_a;count_b
2021-09-07;1;0;1
2021-09-08;3;2;1

Eg make some kind of outer select for the dates, and then feed the dates into a subselect?

Update what I tried without the WHERE clause:

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

The result is WRONG here!

@ Whoever closed the question, I don't see where my question has been answered there! — membersound, Sep 08 '21 at 22:03
Why don't you think it's answered there? Just change your subqueries to `SUM(IF(product_name = 'A', 1, 0)) AS count_a` — Barmar, Sep 08 '21 at 22:04
BTW, if there's a user named "whoever", you just sent them an unnecessary message. — Barmar, Sep 08 '21 at 22:05
My problem is rather how to not having to feed the subquery manually (eg from a script) with all dates found in the db... — membersound, Sep 08 '21 at 22:05
I don't understand that. Why do you think you need to feed the dates manually? That just happens automatically when you group by invocation. — Barmar, Sep 08 '21 at 22:06
You don't need `where invocation = ?` since you want all values of `invocation` — Barmar, Sep 08 '21 at 22:07
I remove the `where` clause and added the result above. That's my problem: the outcome is wrong here... — membersound, Sep 08 '21 at 22:09
Your subqueries need to be correlated. `WHERE p.invocation = products.invocation and p.product_name = 'A'` — Barmar, Sep 08 '21 at 22:10
Ah okay, that works. I see. May I ask, though that works, it may be better to simplify this using your `SUM(IF(...))` suggestion, which also works? — membersound, Sep 08 '21 at 22:17
You'd have to benchmark it to find out which is in your environment. — Barmar, Sep 08 '21 at 22:19
Okay thanks, but it answers my question and both approaches work! — membersound, Sep 08 '21 at 22:20

How to feed a sql subselect with an outer select?

0 Answers0