RE: SQL-help needed
I'm still a beginner myself but try something like SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM CHAMPIONS WHERE CLASS = hd GROUP BY WINNER; I think this will give you something like: COUNT(YEAR) WINNER YEAR 2 carl1957 2 carl1985 1 mattias 1957 1 erik1985 Again I am a beginner and would have to test this to see if it actually gives me the right info or if I would have to tweek it. Respectfully, Ligaya Turmelle Computer Programmer Guam International Country Club 495 Battulo Street Dededo, Guam 96912 Tel: (671) 632-4445 Fax: (671) 632-4440 Reservations: (671) 632-4422 (GICC) -Original Message- From: Carl Schéle, IT, Posten [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 6:50 PM To: [EMAIL PROTECTED] Subject: SQL-help needed Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL-help needed
Carl Schéle, IT, Posten wrote: Hi! I got a table, champions, looking like this: idclass winner_1 winner_2 year - 0 hd carl mattias 1957 1 hs daniel 1982 2 hd erik carl 1985 3 js erik 1974 Imagine I want to see how many times each winner appears where class=hd and which year. In this case the answer would be: 2 carl 1957,1985 1 mattias 1957 1 erik 1985 Please help! Still using old MySQL 3.23.58. The following is close to what you want: CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT); # change the column types to match table champions INSERT INTO champs SELECT winner_1, year FROM champions WHERE class='hd'; INSERT INTO champs SELECT winner_2, year FROM champions WHERE class='hd' AND winner_2 IS NOT NULL; # some of your winner_2 spots are empty. If they're empty strings # instead of NULL, change IS NOT NULL to != '' SELECT * FROM champs ORDER by winner, year; +-+--+ | winner | year | +-+--+ | carl| 1957 | | carl| 1985 | | erik| 1985 | | mattias | 1957 | +-+--+ 4 rows in set (0.01 sec) SELECT count(*), winner FROM champs GROUP BY winner; +--+-+ | count(*) | winner | +--+-+ |2 | carl| |1 | erik| |1 | mattias | +--+-+ 3 rows in set (0.01 sec) === It seems to me that your table design is what makes this difficult. If you changed it to something like the following, where wintype denotes winner_1 or winner_2, this would be easier: id class winner wintype year - 1 hd carl 1 1957 2 hs daniel 1 1982 3 hd erik 1 1985 4 js erik 1 1974 5 hd mattias2 1957 6 hd carl 2 1985 You could then go straight to the select: SELECT * FROM champions WHERE class='hd' ORDER by winner, year; or SELECT count(*), winner FROM champions WHERE class='hd' GROUP BY winner; You could use a variant of the INSERT-SELECTs above to fill the new table, if you decide to go that way. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Help Needed
Ok this should be easy so I'm prolly going to screw it up, but here goes =D Your query should be: SELECT UserID, SUM(points) FROM History WHERE WeekID = 'whatever' GROUP BY UserIdI think that should do it. Someone yell if its wrong though. -Nick I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. Please help! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Help Needed
* Dave I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. I don't know what you have tried or why it did not work for you, but something like this should work: SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL Help Needed
Thanks for your responses but it's not that much help I need with my SQL ;-) None of the 3 suggestions work. Please look at the examples I gave. I need *all* UserIDs regardless of whether they have a record in History that matches both UserID and WeekID. In other words some UserIDs will only exist for WeekID = 2, other for 1 and 2 and so on. Cheers Dave - Original Message - From: Roger Baklund [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Dave [EMAIL PROTECTED] Sent: Thursday, May 23, 2002 6:21 PM Subject: RE: SQL Help Needed * Dave I have 2 tables Users (UserID) History (UserID, WeekID, Points) When a User record is created a record is inserted into History with the current WeekID, so for example data could be : Users History -- - 1 - 10 - 1 - 20 - 1 - 30 - 2 - 40 I want to run a query to return one row for each User row and their points for any given week. In other words somebody asks for all points in Week 1 the result should be : - 10 - 20 - 30 - 0 Or, all points for WeekID 2 would give : - 0 - 0 - 0 - 40 I have tried lots of things but I think my brain is just not giving me the correct solution. I don't know what you have tried or why it did not work for you, but something like this should work: SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SQL Help Needed
* Dave Thanks for your responses but it's not that much help I need with my SQL ;-) None of the 3 suggestions work. Please look at the examples I gave. I need *all* UserIDs regardless of whether they have a record in History that matches both UserID and WeekID. ...and that is what I thought my suggestion would do... SELECT u.UserID, IF(h.Points,h.Points,0) AS Points FROM Users AS u LEFT JOIN History AS h USING(UserID) WHERE ISNULL(WeekID) OR WeekID = 1 ORDER BY u.UserID Maybe you could tell us what error message you got, or in what way the query 'does not work'? -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
