Need to group within a query I'm already grouping

  • Goalie35

    SSC Eights!

    Points: 854

    My company mails letters. The following query returns the total number of letters mailed, split out by the territory/region they were mailed to. Pretty basic:

    select

    b.territory,

    a.letter_code,

    c.mailed_date,

    count(1) as total_letters

    from

    TableA a,

    TableB b,

    TableC c

    where

    c.mailed_date >= @start_date

    and c.mailed_date < @end_date

    group by

    b.territory,

    a.letter_code,

    d.mailed_date

    order by

    b.territory,

    a.letter_code,

    d.mailed_date

    This produces the following:

    Territory | Letter Code | Mailed Date | Total Count

    --------------------------------------------------------------------------

    TPCCAZ01 | AMR-40459 | 2016-08-23 08:30:45.310 | 13

    TPCCAZ01 | AMR-40459 | 2016-08-26 08:30:45.600 | 1

    TPCCAZ01 | AMR-40480 | 2016-08-23 08:30:45.310 | 5

    TPCCAZ01 | AMR-40480 | 2016-08-26 08:30:45.600 | 1

    TPCCAZ01 | AMR-40481 | 2016-08-23 08:30:45.310 | 7

    TPCCAZ02 | AMR-40459 | 2016-08-23 08:30:45.310 | 4

    TPCCAZ02 | AMR-40459 | 2016-08-26 08:30:45.600 | 6

    TPCCAZ02 | AMR-40480 | 2016-08-23 08:30:45.310 | 2

    TPCCAZ02 | AMR-40480 | 2016-08-26 08:30:45.600 | 9

    TPCCAZ02 | AMR-40481 | 2016-08-23 08:30:45.310 | 3

    Now however, they want to group again to display a running total by mailed date, which I'm unsure how to do. So something like the following:

    Territory | Letter Code | Mailed Date | Total Count | Total Count by Date

    -------------------------------------------------------------------------------------------------------------

    TPCCAZ01 | AMR-40459 | 2016-08-23 08:30:45.310 | 13 | 26

    TPCCAZ01 | AMR-40480 | 2016-08-23 08:30:45.310 | 5 | 26

    TPCCAZ01 | AMR-40481 | 2016-08-23 08:30:45.310 | 7 | 26

    TPCCAZ02 | AMR-40459 | 2016-08-23 08:30:45.310 | 4 | 26

    TPCCAZ02 | AMR-40480 | 2016-08-23 08:30:45.310 | 2 | 26

    TPCCAZ02 | AMR-40481 | 2016-08-23 08:30:45.310 | 3 | 26

    TPCCAZ01 | AMR-40480 | 2016-08-26 08:30:45.600 | 1 | 17

    TPCCAZ02 | AMR-40459 | 2016-08-26 08:30:45.600 | 6 | 17

    TPCCAZ02 | AMR-40480 | 2016-08-26 08:30:45.600 | 9 | 17

    TPCCAZ01 | AMR-40459 | 2016-08-26 08:30:45.600 | 1 | 17

    What's the best way to do this?

    Thanks

  • jguffey

    SSC Eights!

    Points: 850

    A window function to count the records over the specified partition (in your case mailed_date) should give you a running total for the day:

    COUNT(*) OVER (PARTITION BY mailed_date order by mailed_date)

  • Luis Cazares

    SSC Guru

    Points: 183583

    Here are 2 options along with sample data. Apparently, your counts per date don't match the real values.

    CREATE TABLE #Sample(

    Territory char(8),

    Letter_Code char(10),

    Mailed_Date Datetime

    );

    INSERT INTO #Sample

    VALUES

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40459', '2016-08-26 08:30:45.600'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40480', '2016-08-26 08:30:45.600'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ01', 'AMR-40481', '2016-08-23 08:30:45.310'),

    ('TPCCAZ02', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ02', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ02', 'AMR-40459', '2016-08-23 08:30:45.310'),

    ('TPCCAZ02', 'AMR-40459', '2016-08-23 08:30:45.310');

    WITH ctePreCount AS(

    SELECT

    territory,

    letter_code,

    mailed_date,

    count(1) as total_letters

    FROM #Sample

    GROUP BY

    territory,

    letter_code,

    mailed_date

    )

    SELECT *,

    SUM(total_letters) OVER( PARTITION BY mailed_date)

    FROM ctePreCount

    ORDER BY

    territory,

    letter_code,

    mailed_date;

    SELECT

    territory,

    letter_code,

    mailed_date,

    count(1) as total_letters,

    SUM(count(1)) OVER( PARTITION BY mailed_date)

    FROM #Sample

    GROUP BY

    territory,

    letter_code,

    mailed_date

    ORDER BY

    territory,

    letter_code,

    mailed_date;

    GO

    DROP TABLE #Sample;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Goalie35

    SSC Eights!

    Points: 854

    Thanks to both of you. This helped immensely!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply