Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Posts
    9

    Unanswered: How do you aggregate on a group?

    This is a silly question but I need help on this.

    I have a table of 50 rows. Each one has customer transaction data.

    I want to create a stored procedure where if I send a customer name parameter, I want to select all the the transactions for that customer and sum the amount of transactions in groupings of 5.

    So for example,

    Customer1 05/15/2004 $10,000
    Customer1 05/15/2004 $10,000
    Customer1 05/15/2004 $10,000
    Customer1 05/15/2004 $10,000
    Customer1 05/15/2004 $10,000

    Customer1 05/15/2004 $10,000
    Customer1 05/15/2004 $10,000

    So the logic is the first set of 5 have a sum > $30000 INSERT into exception
    The second, although the same customer, is ok.

    How do you get a grouping as such in SQL?

    Thanks for the help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by axwack
    This is a silly question but I need help on this.
    no, it's not a silly question, but it has a very easy answer

    there is absolutely no such thing as position within a table

    so, how were you planning on dividing your rows into groups of five?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Does your table have an identity column by chance (this is just a numeric column that assigns a unique number to each row)?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Jun 2003
    Posts
    9
    Yes...I have each row with an id.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's helpful

    which rows would you like to group together, seeing as how they all presumably have different ids?

    please state response in terms of id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Are you looking for something like this?

    --Setup a sample transactions table.
    DECLARE @transactions TABLE(
    transaction_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id VARCHAR(55),
    transaction_date DATETIME,
    transaction_amount MONEY)

    INSERT @transactions(customer_id, transaction_date, transaction_amount)
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer1','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000 UNION ALL
    SELECT 'Customer2','05/15/04',10000

    --Verify it's what you want.
    SELECT * FROM @transactions

    --****This actually begins the query.
    --Declare the grouping number.
    DECLARE @grouping INT

    --This is the number you want to group your transactions by.
    SELECT @grouping = 6

    --This is an example of what this grouping number provides you.
    SELECT (1-1)/@grouping
    SELECT (2-1)/@grouping
    SELECT (3-1)/@grouping
    SELECT (4-1)/@grouping
    SELECT (5-1)/@grouping
    SELECT (6-1)/@grouping
    SELECT (7-1)/@grouping
    SELECT (8-1)/@grouping
    SELECT (9-1)/@grouping
    SELECT (10-1)/@grouping
    SELECT (11-1)/@grouping
    SELECT (12-1)/@grouping
    SELECT (13-1)/@grouping
    SELECT (14-1)/@grouping
    SELECT (15-1)/@grouping
    SELECT (16-1)/@grouping
    SELECT (17-1)/@grouping
    SELECT (18-1)/@grouping
    SELECT (19-1)/@grouping
    SELECT (20-1)/@grouping

    --Create a temp table and insert so all customer_ids are grouped together and the ordinal can be used.
    DECLARE @transaction_grouping TABLE(
    transaction_grouping INT IDENTITY(1,1) PRIMARY KEY,
    customer_id VARCHAR(55),
    transaction_date DATETIME,
    transaction_amount MONEY)

    INSERT @transaction_grouping(
    customer_id,
    transaction_date,
    transaction_amount)

    SELECT
    customer_id,
    transaction_date,
    transaction_amount
    FROM
    @transactions
    ORDER BY
    customer_id,
    transaction_date

    SELECT
    (tg.transaction_grouping-mp.min_point)/@grouping AS group_id,
    tg.customer_id,
    tg.transaction_date,
    SUM(tg.transaction_amount) AS transaction_charge
    FROM
    @transaction_grouping tg
    INNER JOIN (
    SELECT MIN(transaction_grouping) AS min_point, customer_id, transaction_date
    FROM @transaction_grouping
    GROUP BY
    customer_id,
    transaction_date) mp ON tg.customer_id = mp.customer_id
    AND tg.transaction_date = mp.transaction_date
    GROUP BY
    (tg.transaction_grouping-mp.min_point)/@grouping,
    tg.customer_id,
    tg.transaction_date
    ORDER BY
    tg.customer_id,
    tg.group_id
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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