I have two tables for which I am trying query in a certain way-

Table 1

Table 2

I am trying to get the result like this - Result

I have tried different kinds of joins and group by but for whatever reason I do not see the results for the ID type which have a count of 0. So in the example above, I would not see Type 5.

CodePudding user response：

You need the left on the Table1, you want all of those values. You can put more than * in the count operator.

select Table1.ID, count(Table2.ID) from Table1 left join Table2 on Table1.ID = Table2.ID group by Table1.ID

CodePudding user response：

Try this approach, valid in T-SQL (Sql Server) In practice you build 2 temporary tables and left-join them so the type without counts would return a null value and you can COALESCE it at 0

SELECT TYPES.[ID Type], COALESCE(COUNTS.TYPE_COUNT, 0)
FROM
(SELECT DISTINCT [ID Type] FROM TABLE2) TYPES
LEFT JOIN
(SELECT T2.[ID Type], COUNT(*) as TYPE_COUNT
FROM TABLE2
JOIN TABLE1
ON TABLE2.ID = TABLE1.ID
WHERE TABLE1.Status = 'Active') COUNTS
ON TYPES.[ID Type] = COUNTS.[ID Type]

I did not try this so it may contains some syntax error, but my goal is to give you the idea

CodePudding user response：

You need a LEFT JOIN between tab2 and tab1, and a COUNT aggregation.

SELECT tab2.ID_Type, COUNT(tab1.ID) AS Count_of_Active_Cust_Type_A
FROM      tab2 
LEFT JOIN tab1 ON tab2.ID = tab1.ID AND tab1.Cust_Type = 'A'
GROUP BY tab2.ID_Type

Output:

Check the MySQL demo and the SQLite demo.

Note: There's an active Cust Type A for Type 5 as well.