queries to sort the amount of numbers

[Guest]

I have the following Table and want to make a query:

Table1 has the following records:
ID
Shot1
Shot2
Shot3
Shot4
to Shot15

the record of a shot can range from 1 to 10 point

now I want to make a Quere that will sort by
most 10
most 9
most 8
ect. to most 1

is there someone there have
Any ideas???

 

[Guest]

How do you get the "Most's" from "Shot's".

Is Shot1 a record in the ID field OR the name of a field? Are all the Shot's
in fields across the table? If the "Shot's" are field names across the table,
then you table structure is wrong and you can not easily get the data out of
it. It should look more like this:

ShooterID ShotNumber Points
1 1 5
1 2 4
1 3 8
and so on.

Then you could the answer that you are looking for.

 

[Guest]

Try this --
SELECT YourTable.Points, Count(YourTable.Points) AS CountOfPoints
FROM YourTable
GROUP BY YourTable.Points;

 

[Guest]

Mayby I am a little bit stupid, because I cannot do it right.
ShooterID 1 2 3 4

Shot1 10 9 8 10
Shot2 9 8 8 9
Shot3 8 7 8 9
Shot4 9 9 9 9
Shot5 8 10 7 9
Shot6 7 10 6 8
Shot7 6 8 8 8
Shot8 5 7 9 7
Shot9 9 6 6 8
Shot10 8 9 9 8
Shot11 9 9 10 9
Shot12 10 8 8 10
Shot13 10 7 10 10
Shot14 8 7 10 10
Shot15 9 7 9 9

now these 4 shooters has made those above result, and I would like to make a
querie that sort by the number of point 10, 9, 8, 7, 6 etc.

the best shooter is 4 because he has 4 shot of 10 point, 6 shot of 9 point,
4 shot of 8 point. then shooter 1, 3 and 2

can any one spell it in stupid, so i can understand it.
Thanks

 

[Guest]

That's what I was afraid of. You've committed "spreadsheet" which is a very
hideous crime in databases. ;-) . BTW: You aren't stupid. Rather you are
unlearned in the ways of databases (plus don't have the magic decoder ring).
I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

Still it can be salvaged. Below is an SQL statement that totals up each
column and sorts by the raw scores. There are problems with it. The first is
that you have to manually enter a new part of the UNION ALL for each shooter.
If you add, or subtract, a shooter from the table, the query will not work
until you fix it. Next your design allows only up to 255 shooters as that's
all the columns in a table. Actually you'll probably have troubles way before
that as the SQL statement could get too large.

A minor detail is that the SQL below doesn't weigh the points. Both 3 and 1
have the same number of raw points and either could show up in second place.

SELECT "1" AS Shooter, Sum(Shoots.[1]) AS SumOfPoints
FROM Shoots
GROUP BY "1"
UNION ALL
SELECT "2" AS Shooter, Sum(Shoots.[2]) AS SumOfPoints
FROM Shoots
GROUP BY "2"
UNION ALL
SELECT "3" AS Shooter, Sum(Shoots.[3]) AS SumOfPoints
FROM Shoots
GROUP BY "3"
UNION ALL
SELECT "4" AS Shooter, Sum(Shoots.[4]) AS SumOfPoints
FROM Shoots
GROUP BY "4"
Order By 2 Desc ;

Now if your table looked like below, you could get the same answer without
worrying about the number of shoots or shooters.

ShooterID ShotNumber Points
1 1 5
1 2 4
1 3 8
and so on.

Here's the much simpler SQL statement:

SELECT Shoots2.ShooterID,
Sum(Shoots2.Points) AS SumOfPoints
FROM Shoots2
GROUP BY Shoots2.ShooterID
ORDER BY Sum(Shoots2.Points) DESC;