Tricky SQL! - 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

Script Library

Tricky SQL!

Author

Topic

gerten
Starting Member

17 Posts

Posted - 2005-10-18 : 17:37:40

One statistic questiong from an db Resultat.

SELECT m.Namn + ' ' + m.ENamn AS Spelare, SUM(r.Serier) AS Ser, SUM(r.Poang) AS Po, ROUND(SUM(r.Resultat) / SUM(r.Serier * 1.0), 2, 1)AS Snitt, ROUND(SUM(r.Poang * 1.0) / SUM(r.Serier), 2) AS [P Snitt], ROUND(SUM(r.Miss * 1.0 / r.Serier), 2, 1) AS Miss, SUM(r.Miss) AS [Sa Miss], MAX(r.Resultat) AS Bästa, MIN(r.Resultat) AS Sämsta
FROM Resultat r INNER JOIN
Medlemmar m ON r.Spelare = m.ID
WHERE (r.Omgang IN (SELECT DISTINCT TOP 3 Omgang
FROM Resultat
ORDER BY Omgang DESC))
GROUP BY m.Namn + ' ' + m.ENamn
ORDER BY 4 DESC

What i want to do is sort out Min(r.Resultat) where serier is mor than 3, Not WHERE (r.Resultat) >3. More like in Access "Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta]" But that i cant do in SQL

Have also tryed with "WHERE (SELECT MIN(r.Serier) FROM Resultat
Resultat r INNER JOIN
medlemmar m ON r.Spelare = m.ID"
Get lowest result same on all players.

Best reg
Gerten

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:29:25

Min(IIf(r.serier=4,r.resultat,Null)) AS [Sämsta] can be replaced in SQL Server by

Min(Case When r.serier=4 then r.resultat else Null end) AS [Sämsta]

This is the Forum to contribute workable scripts
Hereafter post your questions at Relevent forums like Developer or Transact_SQL

Madhivanan

Failing to plan is Planning to fail

gerten
Starting Member

17 Posts

Posted - 2005-10-19 : 10:45:36

Thank you for your help. Works just perfect

I'll try to put my q in right forum hereby...

Best reg
Gerten

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources