Query - 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

Query

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2011-11-22 : 06:00:27

Hi Guys

I need help with an SQL query. I need to query a database to collect the results of a fishing clubs anual competitions.

Here are my DB fiels

ID (PK)
Date (Comp Date)
Comp (Dam Held)
Points
Angler

I would need the results in the following table. (There will be between 6 and 10 comps for the year)

Angler Comp1 Pts1 Comp2 Pts2 Comp3 Pts3 Comp4 Pts 4 Total Total2*

Angler1 Dam1 87 Dam2 90 Dam3 95 Dam4 100 372 195
Angler2 Dam1 97 Dam2 80 Dam3 90 Dam4 98 365 195

* Total 2 will be their total score less their 2 worst scores.
It must be ordered by Total2 Desc

Hope this makes sense!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-22 : 06:19:24

PIVOT command to the rescue!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 07:32:09

are the listed ones top 4 comp points out of 10?

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

SQLNoob81
Starting Member

38 Posts

Posted - 2011-11-22 : 08:26:19

@visakh16. No, it will list all of them (i only typed 4). It will also give a total of the 10 comps points (Total). Total2 is the total Minus their 2 lowest points!

SQLNoob81
Starting Member

38 Posts

Posted - 2011-11-22 : 08:46:14

Name Comp1 Comp2 Comp3 Comp4 Comp5 Comp6 Comp7 Comp8 Comp9 Comp10 Total Total2
Angler 1 100 90 97 90 89 70 0 67 98 97 798 731
Angler 2 78 79 90 91 100 78 97 99 712 712
Angler 3 98 67 89 100 92 98 100 99 98 841 774
Angler 4 94 99 100 98 100 99 100 96 100 886 792
Angler 5 97 98 86 95 87 87 80 99 100 96 925 752

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:47:15

[code]
SELECT Angler,
MAX(CASE WHEN CompCnt=1 THEN Comp ELSE NULL END) AS Comp1,
SUM(CASE WHEN CompCnt=1 THEN Points ELSE 0 END) AS Pts1,
MAX(CASE WHEN CompCnt=2 THEN Comp ELSE NULL END) AS Comp2,
SUM(CASE WHEN CompCnt=2 THEN Points ELSE 0 END) AS Pts2,
MAX(CASE WHEN CompCnt=3 THEN Comp ELSE NULL END) AS Comp3,
SUM(CASE WHEN CompCnt=3 THEN Points ELSE 0 END) AS Pts3,
MAX(CASE WHEN CompCnt=4 THEN Comp ELSE NULL END) AS Comp4,
SUM(CASE WHEN CompCnt=4 THEN Points ELSE 0 END) AS Pts4,
MAX(CASE WHEN CompCnt=5 THEN Comp ELSE NULL END) AS Comp5,
SUM(CASE WHEN CompCnt=5 THEN Points ELSE 0 END) AS Pts5,
...
MAX(CASE WHEN CompCnt=10 THEN Comp ELSE NULL END) AS Comp10,
SUM(CASE WHEN CompCnt=10 THEN Points ELSE 0 END) AS Pts10,
SUM(Points) AS Total,
SUM(CASE WHEN PtCnt>2 THEN Points ELSE 0 END) AS Total2
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Angler ORDER BY Comp) AS CompCnt,
ROW_NUMBER() OVER (PARTITION BY Angler ORDER BY Points) AS PtCnt,*
FROM Table
)t
GROUP BY Angler
[/code]

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

SQLNoob81
Starting Member

38 Posts

Posted - 2011-11-23 : 03:49:42

Hi visakh16.

That almost does it, the only difference I need is that Name of comp1 must be the heading and all alglers results below it, your was mixes all the comps up.

Angler Dam 1 Name Dam 2 Name Dame 3 Name ... Total total2
Joe 89 90 100 279 100
Soap 95 100 99 295 100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-23 : 05:06:25

so headings will be values in Comp?

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

SQLNoob81
Starting Member

38 Posts

Posted - 2011-11-23 : 05:32:58

Distinct Values of Comp coloumn!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-23 : 06:03:04

then you need this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources