locked
Sum function not working for me RRS feed

  • Question

  • Hi

    I have created a sql query and need to get the total of the field:  CON.new_TotalNoofPlanMembers.
    This field  can contains an integer value from range: 1-10.
    I need to get the grand total of this field - my code is not working and I can't figure out how to get this working.

    SELECT 
    	CON.new_MembershipNumber			AS 'Membership ID',
    	CON.FullName					AS 'Member Name',
    	CON.new_SalesChannelOnlyforPrivateMembersName	AS 'Bll To Corp. Name',
    	CON.new_TotalNoofPlanMembers			AS 'Total # Plan Members',
    	
    	(SELECT SUM(CON.new_TotalNoofPlanMembers)) as 'test'
                           
    
    FROM
    	dbo.FilteredContact CON



    Wednesday, July 17, 2013 9:05 AM

All replies

  • Hi,

    Try by adding groupby function

    SELECT CON.new_MembershipNumber AS 'Membership ID', CON.FullName AS 'Member Name', CON.new_SalesChannelOnlyforPrivateMembersName AS 'Bll To Corp. Name', CON.new_TotalNoofPlanMembers AS 'Total # Plan Members', (SELECT SUM(CON.new_TotalNoofPlanMembers)) as 'test' FROM dbo.FilteredContact CON

    Groupby

    con.new_membershipnumber,con.fullname,con.new_saleschannelonlyforprivatemembersname,con.new_totalnoofplanmembers


    VidhiyaM


    • Edited by VidhiyaM Wednesday, July 17, 2013 11:59 AM Adding query
    Wednesday, July 17, 2013 11:36 AM
  • To scope your Grand Total to the unique set of Membership ID, Name, Bill To Corp Name, and Total # Plan Members, you would want to execute this.

    SELECT
    	CON.new_MembershipNumber AS [Membership ID], 
    	CON.FullName AS [Member Name],
    	CON.new_SalesChannelOnlyforPrivateMembersName AS [Bill To Corp. Name],
    	CON.new_TotalNoofPlanMembers AS [Total # Plan Members],
    	SUM(CON.new_TotalNoofPlanMembers) AS [Test]
    FROM
    	dbo.FilteredContact CON
    GROUP BY
    	CON.new_MembershipNumber,
    	CON.FullName,
    	CON.new_SalesChannelOnlyforPrivateMembersName,
    	CON.new_TotalNoofPlanMembers

    However, that's probably not what your looking for.  This query might give you something more meaningful where we get the grand total of TotalNoofPlanMembers by Membership ID and then include the additional columns:

    SELECT
    	CON.new_MembershipNumber AS [Membership ID], 
    	CON.FullName AS [Member Name],
    	CON.new_SalesChannelOnlyforPrivateMembersName AS [Bill To Corp. Name],
    	SummaryByMembershipNumber.[Total # Plan Members]
    FROM
    	(
    	SELECT
    		sCON.new_MembershipNumber,
    		SUM(sCON.new_TotalNoofPlanMembers) AS [Total # Plan Members]
    	FROM
    		@FilteredContact sCON
    	GROUP BY
    		sCON.new_MembershipNumber
    	) 
    	AS SummaryByMembershipNumber
    	INNER JOIN
    	@FilteredContact CON
    		ON	CON.new_MembershipNumber = SummaryByMembershipNumber.new_MembershipNumber
    Hope it helps, good luck.
    Wednesday, July 17, 2013 6:51 PM