Aggregate Functions (SUM) using CAML

Sep 12, 2013 at 9:17 PM
Edited Sep 12, 2013 at 9:17 PM
I'm trying to use aggregate functions in my CAML query specifically the SUM function.

I have a list that has a UserName column and an Amount column. I'm using SPServices to pull the list into a CEWP which I can do with no problem however I need to group the items by UserName and sum the Amount column for each user.

At the moment I'm stuck trying to SUM the Amount column. I simplified the sample below. I'm only pulling Amount column but cannot get it to sum. The CAML query is showing all items on the list without summing.
<script language="javascript" type="text/javascript">
$(document).ready(function() {
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "SampleList",
    CAMLViewFields: "<ViewFields><FieldRef Name='Amount' Type='SUM'/></ViewFields>",
    CAMLQuery: "<Query><Aggregations Value='Number'><FieldRef Name='Amount' Type='SUM'/></Aggregations></Query>",   
    completefunc: function (xData, Status) {
      $(xData.responseXML).SPFilterNode("z:row").each(function() {
        var varAmount = "<div>" + $(this).attr("ows_Amount") + "</div>";
        $("#calAmount").append(varAmount);
      });
    }
  });
});
</script>
<div>
<div id="calAmount" style="float:left; padding:5px;"></div>
</div>
Can someone help me out?
Sep 13, 2013 at 2:49 AM
I don't thing Aggregations is valid CAML... Here is MS reference:

Paul


--

_________
Paul T

Coordinator
Sep 13, 2013 at 3:41 AM
That's correct, Paul. I think it's available in CSOM, but the SOAP Web Services haven't been updated since 2007.

M.
Sep 16, 2013 at 6:09 PM
Can anyone please recommend the correct way of doing an aggregate sum? I'm stuck on this.

Ultimately I need to Sum the Amount for each user and then sort by the Total Sum Amount from highest to Lowest.

Thanks.
Coordinator
Sep 18, 2013 at 4:09 AM
Edited Oct 3, 2013 at 4:59 PM
Just do the summing client side.

M.
Sep 27, 2013 at 10:11 PM
Edited Sep 27, 2013 at 10:14 PM
I was able to finally accomplish what I needed, however I did not use SP Services. Instead, I used a DVWP and modified the code by following an article writen by non other than our very own Marc Anderson. Thanks Marc!

http://sympmarc.com/2009/05/20/showing-subtotals-in-a-dvwp-sorted-by-the-subtotals/#viewSource
Coordinator
Oct 3, 2013 at 5:00 PM
Well, at least I solved your problem for you!

As I mentioned, you could simply retrieve all of the items needed and do the summing in your script on the client side.

M.
Nov 18, 2013 at 10:14 PM
Hi Marc,

I have a question that maybe you can help me with. As I mentioned above, I used your article linked above to do an aggregate sum and this is working great. However, I've been asked to only show the first or top 5 items. Can you recommend a way of doing that? In SPD I tried using Paging and then Limit to 5 Items, but this is pulling the first 5 items of the list and THEN doing the aggregate sum which is incorrect. I need to pull all the records in the list, do the aggregate sum, sort them, but then only show the first 5 totals. I hope that makes sense.

Hope you can help me out.
J.
Coordinator
Nov 18, 2013 at 10:30 PM
I assume you're still in a DVWP (XSL)?

Are you sorting in your CAML? Can you show what you've tried?

M.
Nov 18, 2013 at 10:48 PM
Hi Marc,

Yes, I'm still in a DVWP.

Here is what I have.

                <xsl:template name="dvt_1">
                    <xsl:variable name="dvt_StyleName">Table</xsl:variable>
                    <xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row"/>
                    <xsl:variable name="dvt_RowCount" select="count($Rows)"/>
                    <xsl:variable name="IsEmpty" select="$dvt_RowCount = 0" />
                    <xsl:variable name="dvt_IsEmpty" select="$dvt_RowCount = 0"/>

                    <xsl:choose>
                        <xsl:when test="$dvt_IsEmpty">
                            <xsl:call-template name="dvt_1.empty"/>
        </xsl:when>
                        <xsl:otherwise>
            <table border="0" width="100%" cellpadding="2" cellspacing="0">
                <tr valign="top">
                    <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
                        <th class="ms-vh" width="1%" nowrap="nowrap"></th>
                    </xsl:if>
                    <th class="ms-vh" nowrap="nowrap"><b>Anesthesiologist</b></th>
                    <th class="ms-vh" nowrap="nowrap"><b>Total Hours Intubated</b></th>
                </tr>
                <xsl:call-template name="dvt_1.body">
                    <xsl:with-param name="Rows" select="$Rows"/>
                    </xsl:call-template>
            </table>
        </xsl:otherwise>
                    </xsl:choose>
                    </xsl:template>
                <xsl:template name="dvt_1.body">
                    <xsl:param name="Rows"/>
                    <xsl:for-each select="$Rows[not(@DVAnesthesiologist = preceding-sibling::*/@DVAnesthesiologist)]">
                    <xsl:sort select="sum($Rows[current()/@DVAnesthesiologist=@DVAnesthesiologist]/@TotalHoursIntubated)" data-type="number" order="ascending" />
                    <xsl:call-template name="dvt_1.rowview">
                        <xsl:with-param name="Rows" select="$Rows" />
                    </xsl:call-template>
                    </xsl:for-each>
</xsl:template>
                <xsl:template name="dvt_1.rowview">
                <xsl:param name="Rows"/>
    <tr>
        <xsl:if test="position() mod 2 = 1">
            <xsl:attribute name="class">ms-alternating</xsl:attribute>
        </xsl:if>
        <xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
            <td class="ms-vb" width="1%" nowrap="nowrap">
                <span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view"></span>
            </td>
        </xsl:if>
        <td class="ms-vb">
            <xsl:value-of select="@DVAnesthesiologist"/>
        </td>
        <td class="ms-vb">
            <xsl:value-of select="format-number(sum($Rows[current()/@DVAnesthesiologist=@DVAnesthesiologist]/@TotalHoursIntubated), '##.##')"/>
        </td>
    </tr>
</xsl:template>
                <xsl:template name="dvt_1.empty">
                    <xsl:variable name="dvt_ViewEmptyText">There are no items to show in this view.</xsl:variable>
    <table border="0" width="100%">
        <tr>
            <td class="ms-vb">
                <xsl:value-of select="$dvt_ViewEmptyText"/>
            </td>
        </tr>
    </table>
</xsl:template></xsl:stylesheet></Xsl>

Coordinator
Nov 18, 2013 at 11:01 PM
You didn't paste in the CAML, so I'll assume that you aren't making any manual changes there. That's probably the solution: add an Order By clause and a RowLimit.

M.
Nov 19, 2013 at 12:10 AM
Hi Marc,

thanks so much, you pointed me in the right directions and I got it to work as needed.

J.
Coordinator
Nov 19, 2013 at 12:22 AM
Excellent.

M.