none
Show Count with 0 rows RRS feed

  • Question

  • I want a list of all active accounts per accountmanager.
    The query below does not show all accoutmanagers because one of them does not have accounts
    If possible I would like to see the  accountmanager in the list with 0 as count value.

    Thanks for any help
    Edwin


    SELECT        ktn_accountmanager, COUNT(*) AS [Active Accounts]
    FROM            dbo.FilteredAccount
    WHERE        (statuscodename = 'Active') AND (ktn_accountmanager IN ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn'))
    GROUP BY ktn_accountmanager
    ORDER BY ktn_accountmanager

    result:
    Vermeulen, Peter 126
    Wang, Dapeng 21
    Friday, October 16, 2009 8:57 AM

Answers

  • I want a list of all active accounts per accountmanager.
    The query below does not show all accoutmanagers because one of them does not have accounts
    If possible I would like to see the  accountmanager in the list with 0 as count value.


    One solution is using LEFT JOIN to the entire GROUP BY set:

    -- Using LEFT JOIN to show 0 count entries in GROUP BY 
    USE AdventureWorks2008; 
    
    SELECT   DISTINCT p.ProductSubcategoryID, 
                      Items = ISNULL(Items,0) 
    FROM     Production.Product p 
             LEFT JOIN (SELECT   ProductSubcategoryID, 
                                 Items = COUNT(* ) 
                        FROM     Production.Product 
                        WHERE    ProductSubcategoryID IS NOT NULL 
                                 AND ProductSubcategoryID < 20 
                        GROUP BY ProductSubcategoryID) x 
               ON x.ProductSubcategoryID = p.ProductSubcategoryID 
    WHERE    p.ProductSubcategoryID IS NOT NULL 
    ORDER BY ProductSubcategoryID 
    /* ProductSubcategoryID	Items
    1	32
    2	43
    3	22
    4	8
    5	3
    6	2
    7	1
    8	3
    9	2
    10	3
    11	3
    12	28
    13	7
    14	33
    15	9
    16	18
    17	14
    18	3
    19	1
    20	0
    21	0
    22	0
    23	0
    24	0
    25	0
    26	0
    27	0
    28	0
    29	0
    30	0
    31	0
    32	0
    33	0
    34	0
    35	0
    36	0
    37	0
    */

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com    Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Friday, October 16, 2009 9:51 AM
    Moderator

All replies

  • try

    SELECT        ktn_accountmanager, COUNT(*) AS [Active Accounts]
    FROM            dbo.FilteredAccount
    WHERE        (statuscodename = 'Active') AND (ktn_accountmanager IN ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn'))
    GROUP BY ktn_accountmanager
    ORDER BY ktn_accountmanager
    having COUNT(*) = 0

    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

    Friday, October 16, 2009 9:00 AM
  • IF I put the having (between the group by and ORDER statement, I have no result at all?

    What I would Like to see is

    Kuntz, Rozenn           0
    Vermeulen, Peter   126
    Wang, Dapeng         21
    Friday, October 16, 2009 9:05 AM
  • Hi Edwin,

    What I understand is Kuntz, Rozenn  must be having no Active account.
    WHat you can do is remove the statuscodename = 'Active') from WHERE and write a CASE like >>>

    SELECT        ktn_accountmanager, SUM(CASE statuscodename WHEN 'Active'
    		THEN 1 ELSE 0 END) AS [Active Accounts]
    FROM            dbo.FilteredAccount
    WHERE        (ktn_accountmanager IN ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn'))
    GROUP BY ktn_accountmanager
    ORDER BY ktn_accountmanager

    Mangal Pardeshi BI
    SQL With Mangal
    Friday, October 16, 2009 9:19 AM
    Moderator
  • I guess that means Rozenn Kuntz does not have any reords in FilteredAccount. Do you have an AccountManager lookup table that you could join to?
    every day is a school day
    Friday, October 16, 2009 9:19 AM
    Moderator
  • I want a list of all active accounts per accountmanager.
    The query below does not show all accoutmanagers because one of them does not have accounts
    If possible I would like to see the  accountmanager in the list with 0 as count value.


    One solution is using LEFT JOIN to the entire GROUP BY set:

    -- Using LEFT JOIN to show 0 count entries in GROUP BY 
    USE AdventureWorks2008; 
    
    SELECT   DISTINCT p.ProductSubcategoryID, 
                      Items = ISNULL(Items,0) 
    FROM     Production.Product p 
             LEFT JOIN (SELECT   ProductSubcategoryID, 
                                 Items = COUNT(* ) 
                        FROM     Production.Product 
                        WHERE    ProductSubcategoryID IS NOT NULL 
                                 AND ProductSubcategoryID < 20 
                        GROUP BY ProductSubcategoryID) x 
               ON x.ProductSubcategoryID = p.ProductSubcategoryID 
    WHERE    p.ProductSubcategoryID IS NOT NULL 
    ORDER BY ProductSubcategoryID 
    /* ProductSubcategoryID	Items
    1	32
    2	43
    3	22
    4	8
    5	3
    6	2
    7	1
    8	3
    9	2
    10	3
    11	3
    12	28
    13	7
    14	33
    15	9
    16	18
    17	14
    18	3
    19	1
    20	0
    21	0
    22	0
    23	0
    24	0
    25	0
    26	0
    27	0
    28	0
    29	0
    30	0
    31	0
    32	0
    33	0
    34	0
    35	0
    36	0
    37	0
    */

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com    Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
    Friday, October 16, 2009 9:51 AM
    Moderator
  • Hi Edwin,

    What I understand is Kuntz, Rozenn  must be having no Active account.
    WHat you can do is remove the statuscodename = 'Active') from WHERE and write a CASE like >>>

    SELECT        ktn_accountmanager, SUM(CASE statuscodename WHEN 'Active'
    
    		THEN 1 ELSE 0 END) AS [Active Accounts]
    
    FROM            dbo.FilteredAccount
    
    WHERE        (ktn_accountmanager IN ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn'))
    
    GROUP BY ktn_accountmanager
    
    ORDER BY ktn_accountmanager
    
    

    Mangal Pardeshi BI
    SQL With Mangal

    I like this simple solution, personally.  No self join is needed.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Friday, October 16, 2009 1:15 PM
    Moderator
  • Yes, this looks like a very simple solution.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
  • Hi Edwin,

    What I understand is Kuntz, Rozenn  must be having no Active account.
    WHat you can do is remove the statuscodename = 'Active') from WHERE and write a CASE like >>>

    SELECT        ktn_accountmanager, SUM(CASE statuscodename WHEN 'Active'
    
    
    
    		THEN 1 ELSE 0 END) AS [Active Accounts]
    
    
    
    FROM            dbo.FilteredAccount
    
    
    
    WHERE        (ktn_accountmanager IN ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn'))
    
    
    
    GROUP BY ktn_accountmanager
    
    
    
    ORDER BY ktn_accountmanager
    
    
    
    

    Mangal Pardeshi BI
    SQL With Mangal

    I like this simple solution, personally.  No self join is needed.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer


    I agree that this is a better solution; however, in case a name might be totally missing from this table and you have a second table that lists only managers, something like might work well:

    -- ----------------------------------------------------------------
    --  This solution assumes that you have a table that has a list of
    --  account managers that you can use to match account manager
    --  names to.
    -- ----------------------------------------------------------------
    select
      a.ktn_accountManager
    , count(b.ktn_AccountManager)
    from dbo.AccountManager a
    left join dbo.FilteredAccount b
      on a.ktn_AccountManager = b.ktn_AccountManager
     and statuscodename = 'Active' 
    where a.ktn_accountmanager IN 
        ('Vermeulen, Peter', 'Wang, Dapeng', 'Kuntz, Rozenn')
    Friday, October 16, 2009 2:10 PM
    Moderator