Need to group within a query I'm already grouping

Question

Need to group within a query I'm already grouping

Goalie35

SSC Eights!

Points: 871
More actions
August 30, 2016 at 9:56 am

#329699

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

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

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

jguffey SSC Eights! Points: 850 More actions · Answer 1

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: 183706 More actions · Answer 2

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-26 08:30:45.600'),

('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'),

('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: 871 More actions · Answer 3

Goalie35

SSC Eights!

Points: 871

August 30, 2016 at 10:35 am

#1898364

Thanks to both of you. This helped immensely!