If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > SUM function query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 272
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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 11:23.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 272
The code looks fine. I will guess that the site has a problem. Uou can download a free copy of SQL Server.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 272
I totally missed the GROUP BY! I am an idiot! I need a Keisaku whacking! ARRGH!
Reply With Quote
  #8 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,451
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On