locked
count + group by RRS feed

  • Question

  • i am using group by clause in sql stmt. if count is 0 also i need to display that rows.

    please provide some example.

    Tuesday, October 11, 2011 8:07 AM

Answers

  • please provide sample data

    create table #t (c1 int,c2 int)

    insert into #t values (1,100)

    insert into #t values (1,200)

    insert into #t values (2,300)

    insert into #t values (2,400)

    insert into #t values (3,0)

    insert into #t values (3,0)

     

     

    select c1,sum(c2) from #t

    group by c1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by KJian_ Monday, October 17, 2011 9:07 AM
    Tuesday, October 11, 2011 8:21 AM
    Answerer
  • Hi Sudeep,
    It seems you are stuck in something below like situation.
    See this one.

    DECLARE @TestA TABLE
    (ID INT,Val NVARCHAR(30))
    
    DECLARE @TestB TABLE
    (ID INT,Loc NVARCHAR(30))
    
    INSERT INTO @TestA  
    SELECT 1,'Sa1' UNION ALL
    SELECT 2,'Sa2' UNION ALL
    SELECT 3,'Sa3' UNION ALL
    SELECT 4,'Sa4' 
    
    INSERT INTO @TestB  
    SELECT 1,'AAA' UNION ALL
    SELECT 1,'BBB' UNION ALL
    SELECT 1,'CCC' UNION ALL
    SELECT 1,'DDD' UNION ALL
    SELECT 2,'PPP' UNION ALL
    SELECT 2,'QQQ' UNION ALL
    SELECT 3,'ZZZ'
    
    
    SELECT A.ID,COUNT(B.ID) AS 'Count' 
    FROM @TestA A LEFT OUTER JOIN @TestB B
    ON A.ID = B.ID 
    GROUP BY A.ID

    Shatrughna.
    • Marked as answer by KJian_ Monday, October 17, 2011 9:07 AM
    Tuesday, October 11, 2011 2:03 PM

All replies

  • please provide sample data

    create table #t (c1 int,c2 int)

    insert into #t values (1,100)

    insert into #t values (1,200)

    insert into #t values (2,300)

    insert into #t values (2,400)

    insert into #t values (3,0)

    insert into #t values (3,0)

     

     

    select c1,sum(c2) from #t

    group by c1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by KJian_ Monday, October 17, 2011 9:07 AM
    Tuesday, October 11, 2011 8:21 AM
    Answerer
  • Hi Sudeep,
    It seems you are stuck in something below like situation.
    See this one.

    DECLARE @TestA TABLE
    (ID INT,Val NVARCHAR(30))
    
    DECLARE @TestB TABLE
    (ID INT,Loc NVARCHAR(30))
    
    INSERT INTO @TestA  
    SELECT 1,'Sa1' UNION ALL
    SELECT 2,'Sa2' UNION ALL
    SELECT 3,'Sa3' UNION ALL
    SELECT 4,'Sa4' 
    
    INSERT INTO @TestB  
    SELECT 1,'AAA' UNION ALL
    SELECT 1,'BBB' UNION ALL
    SELECT 1,'CCC' UNION ALL
    SELECT 1,'DDD' UNION ALL
    SELECT 2,'PPP' UNION ALL
    SELECT 2,'QQQ' UNION ALL
    SELECT 3,'ZZZ'
    
    
    SELECT A.ID,COUNT(B.ID) AS 'Count' 
    FROM @TestA A LEFT OUTER JOIN @TestB B
    ON A.ID = B.ID 
    GROUP BY A.ID

    Shatrughna.
    • Marked as answer by KJian_ Monday, October 17, 2011 9:07 AM
    Tuesday, October 11, 2011 2:03 PM
  • Do you need something like this?
    SELECT aID, bID, COUNT(*) AS TotalCount
    FROM YourTable
    GROUP BY aID, bID
    HAVING COUNT(*) = 0
    
    Hope that helps...
    Sincerely,

    Anooka
    Wednesday, October 12, 2011 12:53 PM