Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010

    Unanswered: Efficient subtotalling

    Hi I have a query below which I need to preform an aggregate sub_total on i.e for each combination of register_id and register_group need to calculate the sum(NoOfWeeks) for that register.

    Before I would of used a nested query but have come across two hurdles , firstly the table contains well over 50,000 of these combinations so it can become quite slow and also I have to do a sum on a formula (I want to do a subtotla on the last column i.e.
    sum((a.end_week - a.start_week) +1) where for all register_id x(10941LQ) and register_group y(MTTHD)

    SELECT a.acad_period, a.register_id, a.register_group, a.start_week, a.end_week, ((a.end_week - a.start_week) +1) as NoOfWeeks

    FROM qlsdat..sttrgprf a
    WHERE acad_period = '09/10'
    and register_id = '10941LQ'
    GROUP BY a.acad_period, a.register_id, a.register_group, a.start_week, a.end_week

    Table results

    09/10 10941LQ MTTHD 1 8 8
    09/10 10941LQ MTTHD 10 16 7
    09/10 10941LQ MTTHD 19 22 4

    Hope this makes sense

    If anybody can even give me a general direction on a best possible solution this would be of great help.

    Many thanks
    Last edited by Ronoc_Yeldarb; 08-11-10 at 10:21.

  2. #2
    Join Date
    Aug 2010
    I also have this problem any help would also be great :P

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Do you want detail AND and aggregate data presented by a single SELECT statement, or are you just looking for how to efficiently compute the aggregates? Either way you need to build an index to support your query, most probably on the register_id and register_group.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Provided Answers: 4
    Can you give us some example data to work with? In the form of an INSERT statement, like:
    INSERT INTO sttrgprf (acad_period, register_id, 
         register_group, start_week, end_week)
    SELECT '01/02', 12, 12, 1, 2
    SELECT ...
    And give the result you want for the given example data.

    As for a hint, I was thinking in the line of using "WITH CUBE" or "WITH ROLLUP". But I will take some study, it's been years since I last used it.
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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