Calculate aggregate value

[jannat]

Does anyone know how to calculate a mode value.

I have to create a report like the following:

for each carrier and calc_date calculate the sum rcf,avg rcf,max rcf and mode rcf.

I don't really know what is that mode function.

Any help will be appriciated.

Thanks in advance.

[Claire]

mode is the most freqent number

This will give you mode value--
select top 1 rcf from MyTable group by rcf order by count(*) desc

[jannat]

But I need all the other fields:
Carrier,calc_date,max(rcf),avg(rcf),
mode(rcf) group by carrier,calc_date.

How to do that?

Thanks.

[skhanal]

Will this work?

select y.Carrier,y.calc_date,max(y.rcf),avg(y.rcf),max(a. counts) as 'Mod'
from yourtable as y
join (select Carrier,calc_date,count(rcf) as 'counts' from yourtable group by carrier,calc_date, rcf) as a
on y.carrier = a.carrier
and y.calc_date = a.calc_date
group by carrier,calc_date

[Claire]

1.run the following query to decide how top value will be

select count(*) from (select carrier from table_name group by carrier,calc_Date )B-->following example is for top 3


2.
select A.carrier,A.calc_date,mode = value,[sum],[max],[avg] from
(select top 3 carrier,calc_date,value from table_name group by carrier,calc_date,value order by count(*) desc)A
inner join
(select CARRIER,CALC_dATE,sum(b.value)as [sum],max(b.value) as [max],avg(b.value)as [avg] from table_name group by carrier,calc_date)B
on A.carrier = B.carrier and A.calc_Date = B.calc_Date

[jannat]

Referring to skhanal's reply:

I am not sure if mode means max(counts) or the value of the column which contains the max counts.

Can you please clarify this?

Thanks.

[Claire]

first run
select Carrier,calc_date,count(value) as 'counts' from b group by carrier,calc_date,value

you will know how does skhanal express the mode.

mode is the value which happend most frequent

[jannat]

Referring to skhanal's reply:

I am not sure if mode means max(counts) or the value of the column which contains the max counts.

Can you please clarify this?

Thanks.

[MAK]

aren't you repeating the same question again?

[jannat]

accidentally, but do you know Mak what's the answer?

[skhanal]

What I have done in inner select is

select Carrier,calc_date,count(rcf) as 'counts' from yourtable group by carrier,calc_date, rcf

this returns counts for each rcf grouped by carrier and calc_date. Your mod is the highest of this number for a given carrier and calc_date.

I join this inner select to outer to get the rest of the result.

I am getting MOD for each carrier and calc_date combination not for all values.

Did you try to run it on your case?

[jannat]

Yes I did, but it's not the max count that I am looking for, what I am looking for is the value of that column which has the max count. From your query I am getting the the highest number, but I need to find out the value of that column which has the highest number.

I hope I was able to explain.

Thanks.

[skhanal]

Oh.

How about this.

select a.carrier, a.calc_date,b.maxrcf, b.avgrcf, c.mode as 'mode'
from
(select Carrier,calc_date,max(rcf) as 'maxrcf',avg(rcf) as 'avgrcf'
from yourtable
group by carrier,calc_date) as b
join
(select a.Carrier,a.calc_date,a.rcf as 'mode'
from yourtable a
group by a.carrier,a.calc_date, a.rcf
having count(a.rcf) >=
(select count(b.rcf) from yourtable b where a.carrier = b.carrier and a.calc_date = b.calc_date)) as c
on b.carrier = c.carrier and b.calc_date = c.calc_date