Combine two queries into one

Question

In the first query, I need to have the count for each type regardless of whether that’s read or not (unread can be true or false). In the second query, I need the count for all types only for when unread is false. Currently I am calling the database two times, once for the first query and once for the second one. Is there anyway that I can call the database only once to get the results for both queries? 

Please note that my queries are in HQL, I wrote the SQL version of them b/c I think its easier; so ideally I need to have a solution that works for HQL too. Thanks.  

--Need the count regardless if is_read is true or false. 
--In order to get total of all rows, I add up each total for each group in the Java code:

select type_id as type, count(distinct ta.id) as totalId 
  from tableA as ta 
       inner join tableB tb on ta.id = tb.id 
       inner join tableC tc on tb.col1= tc.col2 
 where tc.col2= 6 
   and tb.is_deleted = 'N' 
 group by type_id 

--Need the count only for is_read = false. 
--This query gives the total for all unread rows so no group by needed:
    
select count(distinct ta.id) as totalId 
  from tableA as ta 
       inner join tableB tb on ta.id =tb.id 
       inner join tableC tc on tb.col1 = tc.col2 
 where tc.col2= 6 
   and tb.is_deleted = 'N'
   and tb.is_read = 'N' 

Answer 1

you can try this

SELECT 
ta.typeId AS type,
COUNT(DISTINCT ta.id) AS totalId,
COUNT(DISTINCT CASE WHEN tb.isRead = 'N' THEN ta.id END) AS unreadTotalId
FROM 
TableA ta 
INNER JOIN 
ta.tableB tb 
INNER JOIN 
tb.tableC tc  
WHERE 
tc.col2 = 6 AND tb.isDeleted = 'N' 
GROUP BY 
ta.typeId;