June 8, 2009 at 5:03 pm
I need to retrieve a list that sums up all the [gac] per [custn].
The goal will be one GAC per customer number showing the total sum in the [ytd_stat] column
the database, sometimes has more than one [gac] per customer that has not been summed up.
This is because most of the posts only show total sums in the [YTD_stat] kolumn.
The [ytd_stat] is only to show the total sum of each GAC
in the exampel below
I need the total [ydt_stat] for [gac]61 & [gac]62 for [custn]1025.
etc etc for each [custn]
this query: from select * from dbo.YTD_nni
custnprodgacpersytd_stat
1025608601148355.25
1025618611-3051.00
1025616611-12456.00
102562962167.76
1025628621818.70
1025621621200.75
1025658651754.60
1003628622387.00
100399898241.00
The results I need to get:
custnprodgacpersytd_stat
1025608601148355.25
1025618611-15507.00
10256296211087.21
1025658651754.60
1003628622387.00
100399898241.00
to create the table.
CREATE TABLE [dbo].[YTD_nni](
[Custn] [nvarchar](50) NULL,
[Prod] [nvarchar](50) NULL,
[GAC] [nvarchar](50) NULL,
[pers] [nvarchar](50) NULL,
[YTD_stat] [nvarchar](50) NULL
) ON [PRIMARY]
June 8, 2009 at 5:20 pm
Hi,
you need an aggregate function on each of the columns in your result set, I can't infer what the aggregate functions you need to apply to prod and pers, are you have the maximum value? or the first occuring value?
You will need to group by custn and gac, and apply some aggregate function to prod and pers:
select
custn, some_aggregate_function(prod), some_aggregate_function(pers), gac, sum(ytd_stat) as ytd_stat
from dbo.YTD_nni
group by custn, gac
///Edit added missing column, also, if it's the first occuring prod or pers, a simple group by isn't going to work.
June 8, 2009 at 5:32 pm
To allistair
The aggregate function for prod and pers is a problem for me.
the pers will always be the same for each [custn].
I need to group each [prod]
but I don't know what aggregate i can use, a bit lost here I'm afraid
June 8, 2009 at 5:43 pm
It would be the first occurring value.
Each prod will be unique when summed up.
the pers will be the same for each custn
June 8, 2009 at 5:43 pm
What value of prod do you want in the output? minimum, maximum etc?
Going by your earlier post max(convert(int,prod)) as prod would do the trick... so that means:
select
custn, max(convert(int,prod)), max(pers) as pers, gac, sum(ytd_stat) as ytd_stat
from dbo.YTD_nni
group by custn, gac
Search the help/books online for a full list of aggregate functions.
Hope this helps!
June 8, 2009 at 5:48 pm
Sorry, didn't fully read your last post, you want the first value, right?
gis a second, need to test this
June 8, 2009 at 6:17 pm
I think you did it! This query seems to do the trick:
select
custn
, max(convert(int,Prod))as prod
, max(pers) as pers
, gac
, sum(cast(YTD_stat as float)) as ytd_stat
from dbo.YTD_nni
group by Custn, gac
order by custn
June 8, 2009 at 6:29 pm
This will select the maximum value of prod, not the first!
I haven't got an elegant solution for this yet 🙁
Can anyone help?
-- this is OP's data/table
CREATE TABLE [dbo].[YTD_nni](
[Custn] [nvarchar](50) NULL,
[Prod] [nvarchar](50) NULL,
[GAC] [nvarchar](50) NULL,
[pers] [nvarchar](50) NULL,
[YTD_stat] [nvarchar](50) NULL)
insert into YTD_nni
select '1025', '608', '60', '1', '148355.25' union all
select '1025', '618', '61', '1', '-3051.00 ' union all
select '1025', '616', '61', '1', '-12456.00' union all
select '1025', '629', '62', '1', '67.76 ' union all
select '1025', '628', '62', '1', '818.70 ' union all
select '1025', '621', '62', '1', '200.75 ' union all
select '1025', '658', '65', '1', '754.60 ' union all
select '1003', '628', '62', '2', '387.00 ' union all
select '1003', '998', '98', '2', '41.00 '
-- think we need a table with identity to use row_number() ...
create table #YTD_temp (
ID int IDENTITY(1,1),
[Custn] [nvarchar](50) NULL,
[Prod] [nvarchar](50) NULL,
[GAC] [nvarchar](50) NULL,
[pers] [nvarchar](50) NULL,
[YTD_stat] [nvarchar](50) NULL)
insert into #ytd_temp
(custn,prod,gac,pers,ytd_stat)
select * from YTD_nni
-- need a solution???
drop table #ytd_temp
--drop table YTD_nni --///editcommented out drop table
June 8, 2009 at 6:41 pm
Whats wrong with this query (your [brilliant]suggestion):
select
Custn
, max(convert(int,Prod))as prod
, max(pers) as pers
, gac
, sum(cast(YTD_stat as float)) as ytd_stat
from dbo.YTD_nni
group by Custn, gac
The results below seem to be exactly what I need
Custnprodpersgacytd_stat
1003628262387
100399829841
1011628262665
101199829852
1025608160148355,25
1025618161-15507
10256291621087,21
June 8, 2009 at 6:48 pm
Hey Steve,
this'll work so long as you are after the maximum value of prod, rather than the first.
I thought you said in an earlier post you are after the first value of prod?
Allister
June 8, 2009 at 6:56 pm
What I need is the sum of the ytd_stat for each GAC.
that means one GAC per customer. The problem was to sum up (ydt_stat) for each GAC since some GACs were listed 2 or more times per customer [custn].
The prod column is actually a qualifyier for some GAC's, which is why they apperar to be duplicates. Prod results doesn't matter. I used MIN( instead of MAX( and get the same results.
My checks seem to prove your solution works exactly as I needed it to.
I now get the total ytd_stat for each GAC, those listed once and those listed twice or more and needed to be summed together.
Thanks so much for you help, I realy appreciated it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply