How count same columns from few tables in one SQL query

I have five tables, TDetective, TMonths, TProduct1, TProduct2 and TProduct3. I want to have a result set with four columns:

• first is Detective name
• second is count - how many TProduct1 are assigned to this Detective in special months
• third is count - how many TProduct2 are assigned to this Detective in special months
• and fourth is count - how many TProduct3 are assigned to this Detective in special months.

Please help me.

TDetective

    |id     |      |Detective|  

TMonths

    |id     |      |Months |  

TProduct1

  |id  |    |RequestDay|    |Mobile|    |Operator|    |Subjects|    |OccurrenceMode|    |Detective|    |Months|  

TProduct2

  |id  |    |RequestDay|    |Mobile|    |Operator|    |Subjects|    |OccurrenceMode|    |Detective|    |Months|  

TProduct3

  |id  |    |RequestDay|    |Mobile|    |Operator|    |Subjects|    |OccurrenceMode|    |Detective|    |Months|  

My SQL query looks like this:

select      TDetective.Detective,      count(TProduct1.id) as countOfDetectiveP1      count(TProduct2.id) as countOfDetectiveP2      count(TProduct3.id) as countOfDetectiveP3  from      TDetective  left outer join        TProduct1 on TDetective.Detective = TProduct1.Detective  where       TProduct1.Months in (select months from TMonths)  left outer join       TProduct2 on TDetective.Detective = TProduct2.Detective  where       TProduct2.Months in (select months from TMonths)  left outer join       TProduct3 on TDetective.Detective = TProduct3.Detective  where       TProduct3.Months in (select months from TMonths)  group by      Detective.Detective  order by       Detective  

Many thanks

https://stackoverflow.com/questions/66928552/how-count-same-columns-from-few-tables-in-one-sql-query April 03, 2021 at 03:02PM