Racing Simulator Database

Table " test "

 uname | pts | track

uname - player name, pts - number of points on the track

Request to withdraw top players:

 SELECT uname, SUM(pts) AS S FROM test GROUP BY uname ORDER BY S DESC

The task is to find the number of tracks the player traveled and bring it to the top, i.e. count the number of lines from the track where this uname is found. Unfortunately in SQL I’m a complete zero, I don’t even know which way to google. While I read about nested queries and have already read that they work very slowly ...

    3 answers 3

    If I understood correctly:

     SELECT uname, SUM(pts) AS S, COUNT(DISTINCT track) as tracks FROM test GROUP BY uname ORDER BY S DESC

    If a separate track for a player can be present only once, distinct can be removed. Otherwise, you can check this sophisticated version for support:

     SELECT uname, SUM(pts) AS S, (select count(*) from (select 1 as a from test p2 where p1.uname=p2.uname group by track) X) as tracks FROM test p1 GROUP BY uname ORDER BY S DESC
    • You understood absolutely true. But both on yours and on the previous answer I get an error Error while parsing the request. [Token line number = 1, Token line offset = 36, Token in error = DISTINCT] using SQL Server Compact 4.0, it turned out that the DISTINCT function is not supported in it :( proof msdn.microsoft.com/ru-ru/library/bb896140. aspx - Lexanom am
    • This is not a function. Then you can sacrifice strict semantics and distinct remove - renegator
    • @Lexanom, suggested another option in the answer. - msi
    • yes, removed distinct, everything turned out. Thank you very much for the operational assistance :) - Lexanom
     SELECT uname, SUM(pts) AS S, COUNT(DISTINCT track) FROM test GROUP BY uname ORDER BY S DESC

      If you need to withdraw all the players, and how much they each scored on the tracks, then this is done as follows:

       SELECT uname, track, SUM(pts) AS S FROM test GROUP BY uname, track ORDER BY S DESC

      If you need to take a separate player and how much he scored on the tracks in total, then this is done like this:

       SELECT uname, track, SUM(pts) AS S FROM test WHERE uname = 'sampleUserName' GROUP BY uname, track ORDER BY S DESC

      If you need to withdraw only one track, on which the player scored the most points, then do this:

       SELECT TOP 1 uname, track, SUM(pts) AS S FROM test WHERE uname = 'sampleUserName' GROUP BY uname, track ORDER BY S DESC

      The question is not very clear, so I offer you several options that I could suggest.