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 2018년 7월 18일 수요일 오전 4:23
    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 2018년 7월 18일 수요일 오전 4:23
    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

    2018년 7월 18일 수요일 오전 4:24
  • 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

    2018년 7월 18일 수요일 오전 5:24
  • Hello team,

    What could be placed in parentheses in front of over in:

    Count(Sum(Total)) Over()

    ?

    Regards

    CloudsInSky


    CloudsInSky

    2018년 7월 19일 목요일 오전 4:56
  • Hello team,

    What could be placed in parentheses in front of over in:

    Count(Sum(Total)) Over()

    ?

    Regards

    CloudsInSky


    CloudsInSky

    if you leave it as is it will give total count of the sum of totals within the entire dataset

    If you add some columns with on over it will calculate the expression over each group based on the column values in the dataset


    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

    2018년 7월 19일 목요일 오전 5:15
  • Hello team,

    What could be placed in parentheses in front of over in:

    Count(Sum(Total)) Over()

    ?

    Regards

    CloudsInSky


    CloudsInSky

    Check this:

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

    2018년 7월 19일 목요일 오후 2:17
    중재자