#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    Help me with the query..


    i want to find the top scorer

    perform(p_ID, goals)


    SELECT DISTINCT perform.p_id, sum(perform.goals) AS [total goals], Count(*) AS [matches played]
    FROM perform
    GROUP BY perform.p_id


    after trying this query i get all the sum of goals of all players. i wanna find the top scorer in the league with the number of goals scored.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Location
    Saint-Petersburg, Russia
    Posts
    236
    Rep Power
    28
    use "order by" to sort them by score and "limit" to limit how many tops to return.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by rodiongork
    use "order by" to sort them by score and "limit" to limit how many tops to return.
    it wont work..

    table perform
    goals p_ID match_ID
    2 1 a1
    3 1 a2
    4 1 a3
    5 2 a4
    6 3 a5
    1 2 a6

    i want the output as (goals 2+3+9 and player id)
    the best scorer in all matches.

    this must be the output

    p_ID goals
    1 9
    error code
    SELECT DISTINCT perform.p_id, sum(perform.goals) AS [total goals], Count(*) AS [matches played]
    FROM perform
    GROUP BY perform.p_id

    goals p_ID matchplayed
    9 1 3
    6 2 2
    6 3 1

    i dont want player 2 and 3 i need only the highest goaler
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by ishan2ma
    it wont work..
    you didn't try what rodiongork suggested

    you didn't use ORDER BY and LIMIT

    also, please note, DISTINCT is not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0

    thanq for your reply


    Originally Posted by r937
    you didn't try what rodiongork suggested

    you didn't use ORDER BY and LIMIT

    also, please note, DISTINCT is not necessary
    please help me where to put the limit and orderby i dont understand it pops an error

    if u can edit me the code please will be a great help
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    Show us waht you used, and the resulting error - then we can help you.

    Also, just as an aside you do not mention what you want to happen if more then one row has the same highest total goals.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    Thanq all THIS worked
    added top 1
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    shedule ( fix_ID, Hometeam, Awayteam, date,Time, venue)

    results (result_ID, FixID(FK), Awayscore, Homescore)

    produce fixtures yet to be played :s

    i want to know how to join these to tables then result_ID becomes null i wanna display date and fix ID.. after joining both i get no data all empty :S
  16. #9
  17. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,648
    Rep Power
    1945
    Originally Posted by ishan2ma
    Thanq all THIS worked
    added top 1
    TOP 1?

    sounds like you are using MS SQL?
    You have posted your question in the MySQL forum.

    If you are using MS SQL, a moderator can move this thread to where it belongs.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    9
    Rep Power
    0
    Originally Posted by MrFujin
    TOP 1?

    sounds like you are using MS SQL?
    You have posted your question in the MySQL forum.

    If you are using MS SQL, a moderator can move this thread to where it belongs.
    ya am using ms sql

IMN logo majestic logo threadwatch logo seochat tools logo