Results 1 to 8 of 8

Thread: SUM problem

  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: SUM problem

    Code:
    SQL = "SELECT * FROM PurchaseCardCostCodes "
    SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON PurchaseCardCostCodes.costcode_id 
    = PurchaseCardTransactionLog.costcode_id "
    SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner = '"&User&"'" 
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_id = '"&RadioButtonVal&"'"
    SQL = SQL & " SELECT SUM(tl_net) AS nettot FROM PurchaseCardTransactionLog"
    The ASP code doesn't recognise:

    Code:
    <% nettot = objRec("nettot").Value%>
    <td><%=nettot%></td>
    The Error is :
    Item cannot be found in the collection corresponding to the requested name or ordinal.

    I've tried the SUM function on its own, and it works fine, but part of a bigger SQL statement and I get problems

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the sql you have does not make sense

    it looks like two separate sql statements just smooshed together
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    15
    Yeah, I guess changing the last line to

    SQL = SQL & " SUM(PurchaseCardTransactionLog.tl_net) AS nettot FROM PurchaseCardTransactionLog"

    won't work either

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i think i see where you want to go with that

    try this:
    PHP Code:
    select CC.col1
         
    CC.col2
         
    CC.col3
         
    sum(TL.tl_net) as nettot
      from PurchaseCardCostCodes  CC
    inner
      join PurchaseCardTransactionLog TL
        on CC
    .costcode_id 
         
    TL.costcode_id 
     where TL
    .tl_date between [i]DateOne[/i
                          and [
    i]DateTwo[/i]
       and 
    CC.costcode_owner = [i]User[/i
       and 
    CC.costcode_id = [i]RadioButtonVal[/i]
    group
        by CC
    .col1
         
    CC.col2
         
    CC.col3 
    since you are grouping TL rows to each CC row, you cannot use SELECT *

    you need to list the columns you want in the SELECT list and also the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2003
    Posts
    15
    Hmm, had a go, but I get more errors:

    Ambiguous column name 'costcode_id'.


    The bold bit is not from the costcode table, that's where the error is, I think.

    Code:
    SQL = "SELECT costcode_id, costcode_bc, costcode_unit, costcode_activity, costcode_location, costcode_dept, costcode_owner, SUM(PurchaseCardTransactionLog.tl_net) AS nettot 
    
    FROM PurchaseCardCostCodes"
    
    SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON PurchaseCardCostCodes.costcode_id = PurchaseCardTransactionLog.costcode_id"
    
    
    SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
    
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner = '"&User&"'" 
    
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_id = '"&RadioButtonVal&"'"
    
    SQL = SQL & " GROUP BY costcode_id, costcode_bc, costcode_unit, costcode_activity, costcode_location, costcode_dept, costcode_owner"

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's the very first column after the SELECT keyword

    there are two columns in your query with that name

    you need to qualify which one you want:

    SELECT PurchaseCardCostCodes.costcode_id, ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2003
    Posts
    15

    Thumbs up

    Getting there...but still probs:

    Code:
    SQL = "SELECT PurchaseCardTransactionLog.costcode_id, 
    PurchaseCardCostCodes.costcode_bc, 
    PurchaseCardCostCodes.costcode_unit, 
    PurchaseCardCostCodes.costcode_activity, 
    PurchaseCardCostCodes.costcode_location, 
    PurchaseCardCostCodes.costcode_dept, 
    PurchaseCardCostCodes.costcode_owner, SUM
    (PurchaseCardTransactionLog.tl_net) AS nettot FROM PurchaseCardCostCodes "
    
    
    SQL = SQL & " INNER JOIN PurchaseCardTransactionLog ON
    PurchaseCardCostCodes.costcode_id = 
    PurchaseCardTransactionLog.costcode_id"
    
    SQL = SQL & " WHERE PurchaseCardTransactionLog.tl_date 
    BETWEEN '"&DateOne&"' AND '"&DateTwo&"'"
    
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_owner 
    = '"&User&"'" 
    
    SQL = SQL & " AND PurchaseCardCostCodes.costcode_id 
    = '"&RadioButtonVal&"'"
    
    SQL = SQL & " GROUP BY 
    PurchaseCardTransactionLog.costcode_id, 
    PurchaseCardCostCodes.costcode_bc, PurchaseCardCostCodes.costcode_unit, 
    PurchaseCardCostCodes.costcode_activity, 
    PurchaseCardCostCodes.costcode_location, 
    PurchaseCardCostCodes.costcode_dept, 
    PurchaseCardCostCodes.costcode_owner"
    I can output all fields from costcode but not TransactionLog.
    If I add fields from TL to the SELECT and GroupBy statements, makes no difference.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you add fields from TransactionLog, you can no longer do a SUM() over the TransactionLog grouped rows

    i think you need to study grouping a bit more



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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