none
Count(Sum(Total))

답변

  • Hi,

    Can we count an aggregated field?

    What are the options?

    Regards,

    CloudsInSky


    CloudsInSky

    Hi CloudsInSky,

    You could do it like this.

    Create Table Table1(FirstDate Date,Regionname Varchar(50),AMT Money,CTC Money)
    
    insert into Table1 values
    ('2018-2-27','CORPORATE',0,0),
    ('2018-2-27','EAST',3714,2),
    ('2018-2-27','NORTH',15066,24),
    ('2018-2-27','NST',2322,2),
    ('2018-2-27','OTHERS',117,0),
    ('2018-2-27','SOUTH',27727,3),
    ('2018-2-27','WEST',54212,32),
    ('2018-2-28','WEST',54022,19),
    ('2018-2-28','SOUTH',27150,5),
    ('2018-2-28','OTHERS',128,0),
    ('2018-2-28','NST',2289,1),
    ('2018-2-28','NORTH',14438,19),
    ('2018-2-28','EAST',3646,2),
    ('2018-2-28','CORPORATE',0,0)
    
    ;with cte as
    (
    select 
    Regionname,
    sum(AMT) as v_sum
    from Table1
    group by Regionname 
    )
    select count(v_sum) from cte

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 답변으로 제안됨 Ramakrishnan.lh 2018년 7월 13일 금요일 오후 2:10
    • 답변으로 표시됨 cloudsInSky 4시간 19분 전
    2018년 7월 13일 금요일 오전 6:12

모든 응답

  • Hi,

    Can we count an aggregated field?

    What are the options?

    Regards,

    CloudsInSky


    CloudsInSky

    Hi CloudsInSky,

    You could do it like this.

    Create Table Table1(FirstDate Date,Regionname Varchar(50),AMT Money,CTC Money)
    
    insert into Table1 values
    ('2018-2-27','CORPORATE',0,0),
    ('2018-2-27','EAST',3714,2),
    ('2018-2-27','NORTH',15066,24),
    ('2018-2-27','NST',2322,2),
    ('2018-2-27','OTHERS',117,0),
    ('2018-2-27','SOUTH',27727,3),
    ('2018-2-27','WEST',54212,32),
    ('2018-2-28','WEST',54022,19),
    ('2018-2-28','SOUTH',27150,5),
    ('2018-2-28','OTHERS',128,0),
    ('2018-2-28','NST',2289,1),
    ('2018-2-28','NORTH',14438,19),
    ('2018-2-28','EAST',3646,2),
    ('2018-2-28','CORPORATE',0,0)
    
    ;with cte as
    (
    select 
    Regionname,
    sum(AMT) as v_sum
    from Table1
    group by Regionname 
    )
    select count(v_sum) from cte

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 답변으로 제안됨 Ramakrishnan.lh 2018년 7월 13일 금요일 오후 2:10
    • 답변으로 표시됨 cloudsInSky 4시간 19분 전
    2018년 7월 13일 금요일 오전 6:12
  • Hi,

    Can we count an aggregated field?

    What are the options?

    Regards,

    CloudsInSky


    CloudsInSky

    you can

    But you would need to create a derived table for that to find the sum first and then apply count over it

    Doing them in same expression is not allowed as you cant nest aggregate functions


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • 답변으로 제안됨 Ramakrishnan.lh 2018년 7월 13일 금요일 오후 2:10
    2018년 7월 13일 금요일 오전 6:51
  • Use a window function may be an option:

    Count(Sum(Total)) Over()

    2018년 7월 13일 금요일 오후 1:41
    중재자
  • How?

    Regards,

    CloudsInSky


    CloudsInSky

    4시간 18분 전
  • How?

    Regards,

    CloudsInSky


    CloudsInSky

    That is different

    It gives you detailed level data itself with aggregate values repeated for each detail line

    Not sure if thats what you're after


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    3시간 18분 전