get the count of unread messages

I have this requirement with our DB like shown below

I want to get the 3 columns requestType, CategoryGroup, and count of readStatus column

The result will show all the requestType along with categoryGroup and with if readStatus is false then it will show the count of it and if all readStatus is true in that same requestType then return 0.

also if requestType is NULL and Blank in same categoryGroup (from table A, subcategoryId 12 and 13 are from same categoryGroup in table B), then the categoryGroup should shown as BLANK and should hold the count of (readStatus=0) both NULL and BLANK

The query which I have written is returning only the columns and count in respect to readStatus=false and showing the total count of it.

select coalesce( m.requestType, "") as requestType,
  count(m.messageId) as count, 
  s.categoryGroup categoryGroup 
from mb_message m 
join mb_subcategory s on m.subCategoryId = s.subCategoryId 
where m.readStatus=0 
and m.storeId = ? 
and countryCode= ?
and m.modifiedDate >= ?
group by m.requestType, m.subCategoryId;

Expected result for above query is :

Answer

I would do something like:

select
  requesttype, 
  sum(cnt) as cnt,
  categorygroup
from (
  select
    case when a.requesttype is null or a.requesttype = '' 
         then 'BLANK' else a.requesttype end as requesttype,
    case when a.readstatus = 0 then 1 else 0 end as cnt,
    b.categorygroup
  from tablea a
  join tableb b on b.subcategoryid = a.subcategoryid
) x
group by requesttype, categorygroup

Advertisement

Answer