Calculating mode (Math) - 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

General SQL Server Forums

New to SQL Server Programming

Calculating mode (Math)

Author

Topic

julien_roy50
Starting Member

4 Posts

Posted - 2012-02-22 : 09:47:59

Hello guys. I have a table containing many departments with a start date and an end date. Here is my query


Declare @nomTech as nvarchar(50)
Declare @anneeDeConsultation as nvarchar(50)
Set @nomTech = 'Moriphil'
Set @anneeDeConsultation = '2011'

SELECT top 25 nom, datediff(dd,date_creat,date_real) as difference
FROM RDS_REQ_VUE 
join ach_code_util on ach_code_util.CODE_UTIL=RDS_REQ_VUE.code_util_reQ
where code_util_resp = @nomTech
AND ETAT != '99'
AND ETAT != '02'
and (left(CONVERT(VARCHAR(10),DATE_CREAT, 102),4) <= @anneeDeConsultation
and left(CONVERT(VARCHAR(10),DATE_REAL, 102),4) > @anneeDeConsultation 
OR (DATE_REAL IS NULL 
AND left(CONVERT(VARCHAR(10),DATE_CREAT, 102),4) <= @anneeDeConsultation)
AND ETAT != '50')

and there is what it returns

nom----------------------------difference
GRDS - Ens-MEC- École 070------21
GRDS - Ens-SANTÉ- École 070----152
GRDS - Ens-ACI- École 070------49
GRDS - Ens-COIF- École 070-----NULL
GRDS - Ens-ESA- École 070------219
GRDS - Ens-Usinage- École 070--NULL
GRDS - Ens-Usinage- École 070--236
GRDS - Ens-ACI- École 070------27
GRDS - Ens-ACI- École 070------139
GRDS - Ens-FG- École 070-------140
GRDS - Ens-ESA- École 070------NULL
GRDS - Direction école 070-----126
GRDS - Ens-ESA- École 070------39
GRDS - Ens-ACI- École 070------69
GRDS - Ens-ACI- École 070------85
GRDS - Ens-ESA- École 070------NULL
GRDS - Ens-ESA- École 070------272
GRDS - Ens-MEC- École 070------52
GRDS - Ens-ESA- École 070------142
GRDS - Ens-FG- École 070-------18
GRDS - Ens-ESA- École 070------219
GRDS - Direction école 070-----64
GRDS - Ens-FG- École 070-------103
GRDS - Ens-ACI- École 070------84
GRDS - Ens-ESA- École 070------219

What I need to get as a result table is the mode of the colum difference for each department.(The mode is the value that is the most repeated in the series). I have tried many things and look over the net without sucess. Thanks in advance. Hope every thing is clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 09:53:45

this?


SELECT nom,difference
FROM
(
SELECT nom,difference,COUNT(*) AS cnt,ROW_NUMBER() OVER (PARTITION BY nom,difference ORDER BY COUNT(*) DESC) AS Rn
FROM table
GROUP nom
)t
WHERE rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

julien_roy50
Starting Member

4 Posts

Posted - 2012-02-22 : 10:16:53

Thanks for the reply.
First I want to excuse myself since diffenrence is a reserve name. So I'll rather use diffenrences as colum name
Base on your code I have tested the following.


1.SELECT nom,differences
2.FROM
3.(
4.SELECT nom,datediff(dd,date_creat,date_real) as differences,COUNT(*) AS cnt,ROW_NUMBER() OVER (PARTITION BY nom,diffenrences order by count(*) DESC) AS Rn
5.FROM RDS_REQ_VUE 
6.join ach_code_util on ach_code_util.CODE_UTIL=RDS_REQ_VUE.code_util_reQ
7.GROUP nom
8.)t
9.WHERE rn=1

I got the following messages error

quote:

Msg 102, Niveau 15, État 1, Ligne 7
Incorrect syntax near 'nom'.

Maybe your idea is right but i has no luck testing it and i must admit i am not use to this code
ROW_NUMBER() OVER (PARTITION BY nom,diffenrences order by count(*) DESC) AS Rn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-22 : 10:29:42

[code]GROUP BY nom[/code]

KH
[spoiler]Time is always against us[/spoiler]

julien_roy50
Starting Member

4 Posts

Posted - 2012-02-22 : 10:41:21

Yes thanks that one was an obvious mistake.
there is the code now


1.SELECT nom,differences
2.FROM
3.(
4.SELECT nom,datediff(dd,date_creat,date_real) as differences,COUNT(*) AS cnt,ROW_NUMBER() OVER (PARTITION BY nom,diffenrences order by count(*) DESC) AS Rn
5.FROM RDS_REQ_VUE 
6.join ach_code_util on ach_code_util.CODE_UTIL=RDS_REQ_VUE.code_util_reQ
7.GROUP by nom
8.)t
9.WHERE rn=1

and there is the error i get

quote:

Msg 8120, Niveau 16, État 1, Ligne 1
Column 'RDS_REQ_VUE.DATE_CREAT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Niveau 16, État 1, Ligne 1
Column 'RDS_REQ_VUE.DATE_REAL' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 207, Niveau 16, État 1, Ligne 4
Invalid column name 'diffenrences'.

julien_roy50
Starting Member

4 Posts

Posted - 2012-02-22 : 11:43:29

Problem is line 4 column datediff(dd,date_creat,date_real) as differences is not an aggregate function, so any other idea?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources