locked
how to sum the select statement result RRS feed

  • Question

  • Hi,

    How to sum the select statement return result.

    I want to sum all the value that return by select statement.

    Sum(select ceiling(COUNT(*)*1.0/4)from #Result_1 group by Member_ID) not working

    Thanks

    Himal

    Friday, March 4, 2016 10:10 PM

Answers

All replies

  • Try

    ;with cte as (select ceiling(count(*)*1.0/4)  as MemberCount from #Result_1 group by Member_id)

    select sum(MemberCount) as TotalCount from cte

    Or to use as a single statement:

    select sum(MemberCount) as TotalCount

    from (select ceiling(count(*)*1.0/4) as MemberCount

    from #Result_1 group by Member_id) cte



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Proposed as answer by DIEGOCTN Friday, March 4, 2016 10:23 PM
    • Edited by Naomi N Friday, March 4, 2016 11:20 PM
    Friday, March 4, 2016 10:14 PM
  • Hi... Need statement in single line...please explain how to use it
    Friday, March 4, 2016 10:21 PM
  • SELECT SUM(x) AS MemberCount FROM (SELECT CEILING(COUNT(*)*1.0/4.0) AS x FROM #Result_1  GROUP BY Member_id) x
    Sam is quite right, but this should do the trick.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    • Marked as answer by himalkumar Wednesday, March 9, 2016 5:19 PM
    • Edited by Patrick Hurst Thursday, March 10, 2016 2:40 PM
    • Unmarked as answer by pituachMVP Thursday, March 10, 2016 2:59 PM
    • Proposed as answer by pituachMVP Thursday, March 10, 2016 3:01 PM
    Friday, March 4, 2016 10:22 PM
  • Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

    Because of your bad manners, we have to do your typing and guess at everything. And you want toi have people help your for free? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 4, 2016 10:44 PM
  • Hi,

    You can use Naomi's code in one line :-)
    The line break is just to make the code readable for us
    This will work from your application as well (For example in C# code).

    * There is a very tiny change in the code which i inserted. I just added missing ")". except of this, this code is Naomi's code, in one line

    ;with MyCTE as (select ceiling(count(*)*1.0/4) as MemberCount from #Result_1 group by Member_id) select sum(MemberCount) as TotalCount from MyCTE

    * I replaced the name cte with MyCTE since people confuse and when we explain that this code use cte then they think that the we must use "cte" while the "cte" in the code is just a name that can be anything. So I prefer to use in the forum something like MyCTE.

    And now the important part :-)
    The code use something that named Common Table Expression. As mentioned In the code it named MyCTE which is a Common Table Expression. You can (actually you should) learn about this in these links :-) 

    http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER
    http://sqlsunday.com/2013/02/17/cte-tutorial/
    https://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

    I hope this is useful


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Friday, March 4, 2016 11:10 PM
  • Hi Himal,

    Are you sure you marked the right answer? Performing an aggregate function on an expression containing an aggregate is not allowed.


    Sam Zha
    TechNet Community Support

    Thursday, March 10, 2016 6:49 AM