group by/multiple aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

group by/multiple aggregate

Author

Topic

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-14 : 19:27:22

Could i please get help with the following group by/aggregate:

LOAD_DATE SOURCE_SYSTEM Records Records_processed
08-Nov-06 System A 7076 7000
09-Nov-06 System A 8937 8900
30-Oct-06 System B 4054 4000
30-Oct-06 System B 3 1
30-Nov-05 System A 2 1
30-Dec-05 System C 2 1

What I would like is an aggregate/group by of first the cource_system, and sum of records, and records_processed,
for each month (group by month). I am not sure how to do this as the code that i have tried only aggregates it either by month or source system by not both. The result i wish is something like this (that is an aggregate of system by each month, summing the records, and records processed):

LOAD_DATE SOURCE_SYSTEM Records Records_processed
Nov-06 System A 16103 15900
Nov-05 System A 2 1
Oct-06 System B 4057 4001
Dec-06 System C 2 1

Cheers hope that makes sense.

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-14 : 20:05:12

Dont worry guys...i figured it out solo. Cheers
GK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 21:11:03

Care to post your solution ?

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-15 : 00:38:41

Sure...here is the original code.
it wasnt the query itself but the formatting of the original date format. the group by wasnt grouping it by properly:

select right(convert(varchar,load_date,103),7),
source_system,
sum(total_number_of_records_in_file) as Records_File,
sum(total_number_of_records_processed) as Records_Processed,
sum(total_number_of_records_loaded) as Records_Loaded,
sum(total_number_of_records_not_loaded) as Records_Not_Loaded
from load_report
group by right(convert(varchar,load_date,103),7), source_system

extract of end result: Sorry about the formatting

Record_Date*source_system*Records_File*Records_Processed* Records_Loaded*Records_Not_Loaded
10/2006 A 26042 26042 3559 22483
11/2006 A 58286 58286 6222 52065
10/2006 B 6765 6765 6765 0
11/2006 B 18976 18976 18976 0
11/2006 C 52291 52291 51472 819
10/2006 D 5019087 5019087 11952 5007135

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 00:42:32

must have been something like

select datepart(year, load_date), datepart(month, load_date), source_system,
sum(records), sum(records_processed)
from sometable
group by datepart(year, load_date), datepart(month, load_date), source_system

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-15 : 00:52:39

Champinco,

you should use Peter's method as it is more efficient. Furthermore, oonverting the date to string using style 103 will not allow you to sort it by year, month.

you can also use dateadd(month, datediff(month, 0, load_date), 0) to "remove" the day


select dateadd(month, datediff(month, 0, load_date), 0),
       source_system,
       sum(total_number_of_records_in_file)    as Records_File,
       sum(total_number_of_records_processed)  as Records_Processed,
       sum(total_number_of_records_loaded)     as Records_Loaded,
       sum(total_number_of_records_not_loaded) as Records_Not_Loaded
from   load_report
group by dateadd(month, datediff(month, 0, load_date), 0), source_system

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-15 : 00:58:50

Thanks Peter, and KH,
the last post worked perfectly, as did mine, except yes yours is more efficient.
I will be posting up another one soon, that will be nice for you to think about. i cant figure it out in trans sql.
Cheers
GK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 01:11:26

Well, if you are worried about month name, try this

select datepart(year, load_date), datename(month, load_date), source_system,
sum(records), sum(records_processed)
from sometable
group by datepart(year, load_date), datename(month, load_date), source_system

Peter Larsson
Helsingborg, Sweden

Champinco
Yak Posting Veteran

54 Posts

Posted - 2006-11-15 : 01:25:21

Hey guys, maybe one of you can help me with this baby, which is linked to my previous posts on group by and aggreggates.

This code:

SELECT commission_summary.distributor as Distributor,
convert(varchar,commission_summary.commission_month,103) as 'Commission Date',
'$' + convert(varchar(50), sum(commission_summary.payment_this_period_carried_forward),1) as Total
from commission_summary
group by commission_summary.commission_month, distributor
order by commission_summary.commission_month desc, sum(commission_summary.payment_this_period_carried_forward) desc

returns this:

Dist Commission D Total
MA000 01/08/2006 $602,431.96
LY000 01/08/2006 $227,826.73
LX000 01/08/2006 $102,945.67
LB000 01/08/2006 $101,997.54
LW000 01/08/2006 $100,783.15
...
...
MA000 01/07/2006 $743,176.50
LY000 01/07/2006 $224,787.86
LW000 01/07/2006 $168,456.88
LX000 01/07/2006 $96,536.36
LB000 01/07/2006 $95,393.88
...
etc...

What I require is the top 2 values (in $ amount) per month. That is return the top 2 distributors in terms of amount for each month. Note that i have fooled around with the original code in terms of formatting.
Cheers guys, this one is hard for me. I know in oracle you probably could use a partition. I was maybe thinking of an index count (resetting at each month), and then having a Having count > 2.
Cheers
GK

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 02:00:15

Are you using SQL 2000 or SQL 2005?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 02:13:20

[code]-- prepare test data
set dateformat dmy

declare @test table (dist varchar(5), dt datetime, total money)

insert @test
select 'MA000', '01/08/2006', $602431.96 union all
select 'LY000', '01/08/2006', $227826.73 union all
select 'LX000', '01/08/2006', $102945.67 union all
select 'LB000', '01/08/2006', $101997.54 union all
select 'LW000', '01/08/2006', $100783.15 union all
select 'MA000', '01/07/2006', $743176.50 union all
select 'LY000', '01/07/2006', $224787.86 union all
select 'LW000', '01/07/2006', $168456.88 union all
select 'LX000', '01/07/2006', $96536.36 union all
select 'LB000', '01/07/2006', $95393.88

-- do the work
select datepart(year, t.dt) [year],
datename(month, t.dt) monthname,
t.dist,
t.total
from @test t
where t.total in (select top 2 e.total from @test e where datepart(year, e.dt) = datepart(year, t.dt) and datepart(month, e.dt) = datepart(month, t.dt))
order by datepart(year, t.dt),
datepart(month, t.dt),
t.total desc[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources