www.webdeveloper.com
Results 1 to 4 of 4

Thread: SQL SUM function

  1. #1
    Join Date
    Jul 2005
    Posts
    55

    SQL SUM function

    im trying to run this SQL statement:

    Code:
    strSQL = strSQL & "SELECT MESSAGE_TYPE, ORDERS, ZSM_PURCH_INFO, DATE, SUM([ORDERS]) AS TOTALORDERS 
    FROM WORK_COUNT 
    WHERE DATE Like '%" & Date2Get & "%'"
    i keep getting an error message saying: "You tried to execute a query that does not include the specified expression 'MESSAGE_TYPE' as part of an aggregate function"

    when i take out the "SUM([ORDERS]) AS TOTALORDERS", the SQL runs properly. anyone know how to fix this?

    thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    Don't know about using LIKE on a date although I kept that part from your post above, but as far as the rest goes perhaps add a GROUP BY clause and possibly change WHERE to be HAVING:

    strSQL = strSQL & "SELECT MESSAGE_TYPE, ORDERS, ZSM_PURCH_INFO, [DATE], SUM([ORDERS]) AS TOTALORDERS
    FROM WORK_COUNT
    GROUP BY MESSAGE_TYPE, ORDERS, ZSM_PURCH_INFO
    HAVING [DATE] Like '%" & Date2Get & "%'"
    Also Date is a reserved word so I wouldn't suggest actually using that as a field name. But if you do, then at least surround it with brackets.

  3. #3
    Join Date
    Jul 2005
    Posts
    55
    thanks for the reply. it works, however, when i try to put TOTALORDERS in a <TR> to display, it doesnt add the columns up, it only displays the last number in the record.

    here's the code where this happens:

    Code:
    Do While Not rs.EOF
    	mess= rs("MESSAGE_TYPE")
    	ord = rs("ORDERS")
    	zsm = rs ("ZSM_PURCH_INFO")
    	totord=rs("TOTALORDERS")
    %>
    
    <tr class="tablegray">
    <td align="left" valign="top" width="65%"><%= mess %></td>
    <td><%= ord %></td>
    <td><%= zsm %></td>
    </tr>  
    
    <%
     rs.MoveNext
     Loop
    End if
    %>
    
    <tr class="tablegray">
    <td align="left" class="tabledkblue" valign="top" width="65%">Totals</td>
    <td><%= totord %></td>
    </tr> 
    
    <%
    rs.Close
    conn.Close
    Set objRecordset = Nothing
    Set objConnection = Nothing
    %>
    </table>
    how do i fix it so that TOTALORDERS displays the sum of ORDERS, as specified in my SQL from the first post?

    thanks again. i appreciate the help.

  4. #4
    Join Date
    Jan 2003
    Location
    USA
    Posts
    688
    Here's a little something I wrote to myself awhile back about using subqueries which is something you may want to consider as it sounds like you want to show each detail record along with a total for all records:

    Example of one query (QueryB) based on the results of another query (QueryA):

    QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

    QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

    But the following is even faster and allows for more than one field to be returned in QueryA:

    QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

    So QueryA would include all the CustID's for customers starting with A.

    And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).

    I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build from the tblSQLA on the left to other tables that have fields you want to return.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles