Results 1 to 7 of 7

Thread: Using SUM

  1. #1
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Unanswered: Using SUM

    I get this error when I am using a SUM.

    "number of page buffers for cache required"

    I tryed looking this error up to see what it means with no pages found. If any of you know what this error means it would help me.

  2. #2
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    P.s.

    Here is my SQL...

    Select
    EV.Event_Nbr ,
    SE.Sub_Ev_Nbr ,
    SE.Room_charge ,
    SE.Start_Date ,
    SE.Start_Time ,
    SE.End_Time ,
    EV.Event_Name ,
    EV.EvType ,
    EV.Client_Name ,
    SE.Room_Name ,
    EV.BillCatDesc ,
    Sum(Event_Nbr) As EventNumberTotal
    From
    Events EV,
    Subevent SE

    Where
    EV.Event_Nbr = SE.Event_Nbr
    and
    SE.Start_Date >= tartdate
    And
    SE.Start_Date <= :Enddate
    And
    EV.Status_Level = 1

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The query is invalid.

    Select a, b, sum(c)
    from tableA
    GROUP BY a, b
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    Cool Thanks

    I forgot to post my group by. I still got the same error with the group by that I was using but I noticed that my group by was not in the same order as my select. I changed the order so that they matched and it worked. Now to see if my arithmatics on time ranges work.

    Here is my final SQL....

    elect
    Ev.Event_Nbr ,
    SE.Start_Date ,
    SE.Room_Name ,
    SE.Sub_Ev_Nbr ,
    SE.Start_Time ,
    SE.End_Time ,
    EV.Event_Name ,
    EV.EvType ,
    EV.Client_Name ,
    SE.Room_Name ,
    EV.BillCatDesc ,
    (SE.END_TIME - SE.START_TIME) As DailyHours,
    Sum(SE.END_TIME - SE.START_TIME) As EventHours
    From
    Events EV
    Left Join Subevent SE On
    EV.Event_Nbr = SE.Event_Nbr
    Where
    SE.Start_Date >= tartdate
    And
    SE.Start_Date <= :Enddate
    And
    EV.Status_Level = 1

    Group By
    Ev.Event_Nbr ,
    SE.Start_Date ,
    SE.Room_Name ,
    SE.Sub_Ev_Nbr ,
    SE.Start_Time ,
    SE.End_Time ,
    EV.Event_Name ,
    EV.EvType ,
    EV.Client_Name ,
    SE.Room_Name ,
    EV.BillCatDesc

    Order By
    SE.START_TIME,
    Ev.Event_Nbr,
    SE.Room_Name


    My task is to get the hours per day and the total hours per event.

  5. #5
    Join Date
    Apr 2004
    Location
    Arizona
    Posts
    75

    I think this task is imposable.

    I had to rewrite the SQL because it was not totaling on the groups. So I did a subselect and Now I am getting a Singleton Select error.

    Here is my SQL,

    Select
    SE.Event_Nbr ,
    SE.Start_Date ,
    SE.Room_Name ,
    SE.Sub_Ev_Nbr ,
    SE.Event_Type ,
    SE.Start_Time ,
    SE.End_Time ,
    EV.Event_Name ,
    EV.Client_Name ,
    (SE.END_TIME - SE.START_TIME) As DailyHours,
    select
    Sum(SE.END_TIME - SE.START_TIME)
    from
    SubEvent SSE
    where
    SSE.Event_Nbr = SE.Event_Nbr
    Group By
    SSE.END_TIME,
    SSE.START_TIME) As EventHours

    From
    Events EV,
    Subevent SE
    Where
    EV.Event_Nbr = SE.Event_Nbr
    And
    SE.Start_Time >= tartdate
    And
    SE.Start_Time <= :Enddate
    And
    EV.Status_Level >= 1

    Order By
    SE.START_TIME,
    Ev.Event_Nbr,
    SE.Room_Name

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    As an alternative,

    select *
    from
    (select ta.id, sum(ta.fieldB)
    from tableA ta
    group by ta.id) V
    INNER JOIN
    tableB tb ON
    tb.id = V.id
    INNER JOIN
    etc.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r123456 your syntax is lovely, but how does it help?

    ace, your problem is that you want to show individual rows as well as the sum of values over those individual rows

    your query is getting an error because you have a GROUP BY in the subselect

    a subselect in the SELECT list must be a scalar subselect, i.e. return only one value
    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
  •