Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    SUM function query

    Hi guys!

    Hi,

    I am trying to solve the following exercise in order to practice my knowledge in sql. The exercise is located in: The JOIN operation - SQLZOO

    and the one I am stuck with is number 13, could anybody help me out.
    As far as I come out I have many different codes but all of them the editor complains is wrong.

    I thought this was kind of on the right path but seems not to be at all
    Code:
    SELECT mdate,
      team1,
     count(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1
      FROM game JOIN goal ON matchid = id
    thanks in advance!

  2. #2
    Join Date
    Jan 2013
    Posts
    310
    SELECT match_date, home_team_id,
    SUM (CASE WHEN team_id = home_team_id THEN 1 ELSE 0 END) AS score
    FROM Games AS G1, Goals AS G2 ON G1.match_id = G2.match_id;

    Sorry, but I had to bring this code up to ISO-11179 rules. The COUNT() you had will not work; think about what you are counting.

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    Thanks for the answer, however is still not working when I put it in the editor included in the link I included where there is the questions.

    I am really stuck, because when I see your code, logically I am able to understand is just SQL semantics that are wrong, any idea?

    Thanks for the help

  4. #4
    Join Date
    Sep 2003
    Posts
    102
    Missing a group by:

    SELECT mdate,
    team1,
    SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,
    team2,
    Sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score1
    FROM game JOIN goal ON matchid = id
    GROUP by id
    order by mdate

    It didint' give me an error..but it didn't say pass either (edit: can't figure out the sort order)
    Last edited by ontheDB; 02-01-13 at 12:23.

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    Looks good I guess from here I can try to get the correct result. Thanks a lot!! If I get the correct answer I will post it back, or ask for more help

    Thanks again for the help

  6. #6
    Join Date
    Jan 2013
    Posts
    310
    The code looks fine. I will guess that the site has a problem. Uou can download a free copy of SQL Server.

  7. #7
    Join Date
    Jan 2013
    Posts
    310
    I totally missed the GROUP BY! I am an idiot! I need a Keisaku whacking! ARRGH!

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    Quote Originally Posted by Celko View Post
    I totally missed the GROUP BY! I am an idiot! I need a Keisaku whacking! ARRGH!
    Welcome to my universe!

    TGIF!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2012
    Posts
    52
    Quote Originally Posted by Celko View Post
    I totally missed the GROUP BY! I am an idiot! I need a Keisaku whacking! ARRGH!
    What do you mean? Shall it be
    Code:
    SELECT mdate,
    team1,
    SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,
    team2,
    Sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score1
    FROM game JOIN goal ON matchid = id
    GROUP by id
    GROUP by mdate
    I mean instead of order by group by in the mdate also?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •