none
COUNT(*) and SUM(COUNT(*)) alongside

    Question

  • Hi all,

    Can I achieve something like below without having to use subqueries ?

    SELECT CutomerType,Count(*) as [CountPerCustType], SUM(Count(*)) as [TotalCust] from [Customers]

    Friday, April 05, 2013 10:26 AM

Answers

  • SELECT CutomerType,Count(*) as [CountPerCustType], SUM(Count(*)) over () as [TotalCust] 
    from [Customers]


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Sweets_SQL Friday, April 05, 2013 11:20 AM
    Friday, April 05, 2013 10:38 AM

All replies

  • In your query, COUNT(*) and SUM(COUNT(*)) will always return the same value; what are you exactly trying to archive?

    Olaf Helper

    Blog Xing

    Friday, April 05, 2013 10:35 AM
  • SELECT CutomerType,Count(*) as [CountPerCustType], SUM(Count(*)) over () as [TotalCust] 
    from [Customers]


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Sweets_SQL Friday, April 05, 2013 11:20 AM
    Friday, April 05, 2013 10:38 AM
  • Hi Olaf,

    I agree to what you said above....Trying to achieve something like below so that quickly I can do some percentage calculation....

    CustomerType     Count          Total

    A                           10              40

    B                           20              40

    C                           10               40

    Friday, April 05, 2013 10:38 AM
  • Try the below:

    SELECT 
    	DISTINCT CutomerType, 
    	Count(*) OVER(PARTITION BY CutomerType) AS CountPerCustType, 
    	Count(*) OVER() TotalCust
    from Customers


    Krishnakumar S

    Friday, April 05, 2013 10:43 AM
  • Krishna,

    Code looks to be working great but not exactly there...

    what its giving me is -

    CustomerType     COUNT(*)      TOTAL

     A                              1             4

    B                               1             4

    C                               1             4

    D                               1             4

    When it should be calculating the no. of lines for that customer I think its simply calculating the distinct CustomerType...But I must say that the above code is just superb and can be used elsewhere...

    Friday, April 05, 2013 10:53 AM
  • Can you provide the sample data and expected results, so that we can have a try?

    Krishnakumar S

    Friday, April 05, 2013 11:00 AM
  • CREATE TABLE CUST_TEST (CustType varchar(10),Region varchar(50))
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('A','W1') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('A','W2') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('B','W1') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('A','W3') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('C','W1') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('C','W2') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('C','W3') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('D','W1') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('D','W2') 
    INSERT INTO [CUST_TEST]([CustType],[Region]) VALUES ('D','W4') 

    I want something like below :-

    CustType   Count    Total

    A                3          10

    B                1          10

    C                3          10

    D                3          10

    Friday, April 05, 2013 11:16 AM
  • Instead of a subquery, which will be evaluated per row, you could use a derived table in the FROM clause, like:

    DECLARE @tbl AS TABLE(val varchar);
    INSERT INTO @tbl VALUES ('A');
    INSERT INTO @tbl VALUES ('A');
    INSERT INTO @tbl VALUES ('B');
    INSERT INTO @tbl VALUES ('C');
    
    SELECT val, COUNT(*) AS Cnt, MAX(SumTbl.SumTbl) AS TotlCnt
    FROM @tbl
        ,(SELECT COUNT(*) AS SumTbl FROM @tbl) AS SumTbl
    GROUP BY val


    Olaf Helper

    Blog Xing

    Friday, April 05, 2013 11:17 AM
  • This is working for me:

    SELECT 
    	DISTINCT CustType, 
    	Count(*) OVER(PARTITION BY CustType) AS CountPerCustType, 
    	Count(*) OVER() TotalCust
    from CUST_TEST


    Krishnakumar S

    Friday, April 05, 2013 11:20 AM
  • Thanks Russ Loski,

    The code below gives me exactly what I need.....

    SELECT CutomerType,Count(*) as [CountPerCustType], SUM(Count(*)) over () as [TotalCust] from [Customers]


    • Edited by Sweets_SQL Friday, April 05, 2013 12:15 PM
    Friday, April 05, 2013 11:21 AM