Not able to find out count < 1 when GROUP used

Sudip Bhatt 2,271 Reputation points
2021-01-30T17:08:41.697+00:00

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
62193-ww.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-02-01T03:25:48.07+00:00

    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:
    62274-image.png

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2021-01-30T17:52:31.743+00:00

    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
    
    1 person found this answer helpful.