Not able to find out count < 1 when GROUP used

Question

I have a table and structure like

CREATE TABLE [dbo].[tblTest](  
 ID INT Identity,  
 [C_ID] [int] NULL,  
 [Type] [varchar](max) NULL,  
 [Title] [varchar](max) NULL  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  

Here giving a screen shot which show how data stored liked

I want to find out C_ID wise count when Type is 'GROUP'

C_ID having value 2 has no data having Type = GROUP but my query return nothing

here is my query

select c_id from tblTest  
where Type='GROUP'  
group by c_id  
having count(*)<1  

How to modify the above query as a result it will show count when Type = GROUP data does not exist C_ID wise.
C_ID = 1 has 2 GROUP so it should return C_ID= 1 and count = 2
C_ID = 1 has No GROUP so it should return C_ID = 2 and count =0

same way the query should return C_ID and count when Type will be Lineitem.

Even i tried this but not getting desired output

SELECT *, ROW_NUMBER() OVER(PARTITION BY [Type] Order By C_ID) AS Row_Number    
FROM tblTest    
where Type='GROUP' and C_ID=2  

Please tell me how to write the right query. Thanks

Answer 1

Hi @Sudip Bhatt ，

Or try:

     CREATE TABLE [dbo].[tblTest](  
      ID INT Identity,  
      [C_ID] [int] NULL,  
      [Type] [varchar](max) NULL,  
      [Title] [varchar](max) NULL  
     ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
      
     INSERT INTO [dbo].[tblTest] VALUES(1,'GROUP','Segment'),(1,'GROUP','Loyalty'),  
                                       (1,'Lineitem','Revenue'),(1,'Lineitem','EBITDA'),  
        (2,'Lineitem','Operate'),(2,'Lineitem','Revenue'),  
        (null,null,null)  
      
    select C_ID,sum(case when Type='GROUP' then 1 else 0 end )groupcount,  
    sum(case when Type='Lineitem' then 1  else 0 end)Lineitemcount  
    from [dbo].[tblTest]  
    group by C_ID  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

One of solutions:

select C_ID,
    count(case [Type] when 'GROUP' then 0 end) as GroupCount, 
    count(case [Type] when 'Lineitem' then 0 end) as LineitemCount
from tblTest
group by C_ID