I need getting data from multiple tables, with COUNT included?

Question

I need to create a query from 2 tables, where my company stores e-shop information.

Example of data from the first table:

currentDate: 5.5.2022 | eshopId: 1 | eshopName: test | active: true |

Table 2:

currentDate: 5.5.2022 | eshopId: 1 | orderId: 123 | attribution: direct |

From the first table, I want get how many days in a given period the eshop was active. From the second table, I would like to count all the orders that were attributed directly to our company in the same time period as in the first table.

SELECT i.id, count(*) 
from table1 as i 
    FULL JOIN table1 as e ON e.id= i.id 
WHERE i.active = TRUE 
GROUP BY i.id

I tried merging the same table twice, because after I used count to get amount of inactive dates, I could not use another variable as it was not aggregated. This still does not work. I cannot imagine how I would do this for 3 tables. Can someone please point me in the right direction on how to do this? Thanks.

Answer 1

If there is one row for each day per eshopId and you want to count number of active days along with number of order per eshopId:

SELECT i.eshopId, count(*) 
from table1 as i 
     left join (select eshopId, count(distinct orderId) from table2 group by eshopId) j on i.eshopId=j.eshopId
WHERE i.active = TRUE 
GROUP BY i.eshopId