Results 1 to 19 of 19

Thread: [RESOLVED] SUM Function

Hybrid View

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Resolved [RESOLVED] SUM Function

    hi there.. can someone teach me how to do this.. thanks


    Last edited by __wired__; May 26th, 2009 at 07:21 PM.

  2. #2
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: SUM Function

    SQL Code..
    Code:
    
       SELECT SUM(OrderPrice) AS OrderPrice, SUM(OrderPrice1) AS OrderPrice1, Customer
         FROM YOURTABLE
     GROUP BY Customer
    

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: SUM Function

    thanks for the reply.. can you check my code..
    vb Code:
    1. rs.Open ("SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#"), conn, 3, 3
    it gave me error msg >> "You tried to execute a query that does not include the specified expression 'NAME' as part of an aggregate function".... but when i remove the "NAME"... it will sum all the fields that im trying to sum up.. it shows in 1 row

  4. #4

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: SUM Function

    i paste the wrong code.. sorry.. here's the code..

    vb Code:
    1. rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB GROUP BY NAME WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#", conn, 3, 3

    here's the error:

    but when i remove the WHERE statement... it's working..i need to filter it out by date..

  6. #6

  7. #7
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: SUM Function

    use first the WHERE clause then the GROUP by clause
    rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB
    WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "#
    GROUP BY NAME", conn, 3, 3
    Last edited by wysiwyg327; May 26th, 2009 at 08:58 PM.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: SUM Function

    thanks for the help...

    here's the code...
    vb Code:
    1. rs.Open "SELECT SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF, NAME from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "# GROUP BY NAME", conn, 3, 3

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    one more thing.. i want to display also the other fields not only the "NAME" what should i type?
    Last edited by __wired__; May 26th, 2009 at 10:16 PM.

  10. #10
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: [RESOLVED] SUM Function

    simple as this:
    H_DF, NAME , (new field).....

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    for example.. H_DF, NAME, POSITION is the new field.. should i include it in GROUP BY NAME, POSITION <<< ?

  12. #12
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: [RESOLVED] SUM Function

    sorry my mistake (POSITION = new field), yes you should include it on your GROUP BY clause...hope it works..

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    vb Code:
    1. rs.Open "SELECT NAME, LEVEL, SUM(H_RB)as H_RB, SUM(H_HS) as H_HS, SUM(H_SF) as H_SF, SUM(H_DF) as H_DF from qryMB WHERE TRANDATE>=#" & dtp1.Value & "# and TRANDATE <=#" & dtp2.Value & "# GROUP BY NAME, LEVEL", conn, 3, 3

    i insert "SELECT NAME, LEVEL, SUM(H_RB)as H_RB... and GROUP BY NAME, LEVEL"...
    this is my error..

  14. #14
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: [RESOLVED] SUM Function

    what particular error do you get?

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    when i insert the "LEVEL" in the SELECT and GROUP BY..

  16. #16
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: [RESOLVED] SUM Function

    Ok lets be specific, i tried to analyze your query and compare it to my previous sql queries with the same function as yours..try to analyze this and make some comparisons..

    vb Code:
    1. "SELECT IDno, Project, TaskCode, TaskDescription, DateSave, ISNULL(SUM(RecNo), 0) AS Filesize," & _
    2. "Sum (IsNull(DateDiff(Second, Start1, End1), 0) + IsNull(DateDiff(Second, Start2, End2), 0))/ 3600.00 " & _
    3. "AS Total_Time From Gale WHERE (DateSave BETWEEN '" & Me.dtFromD.Value & "' AND '" & Me.dtToD.Value & "') " & _
    4. "AND (IDno = '" & StrIdno & "') " & _
    5. "GROUP BY IDno, Project, TaskCode, TaskDescription, DateSave " & _
    6. "ORDER BY Project, TaskCode, TaskDescription, DateSave DESC"

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    thanks for you help... i think the word "LEVEL" is a reserved word.. coz other fields are working except the field named "LEVEL"... i totally appreciate your help... GOD BLESS

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Mar 2009
    Posts
    66

    Re: [RESOLVED] SUM Function

    i changed the field name "LEVEL"... now its totally working... yahhoooo... thanks again

  19. #19
    Junior Member wysiwyg327's Avatar
    Join Date
    May 2009
    Location
    App.Path
    Posts
    19

    Re: [RESOLVED] SUM Function

    nice!

Posting Permissions

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



Click Here to Expand Forum to Full Width