Re: Help with Sum(), newbie
if you have a windows machine for a front end, download corereader from http://corereader.com/ it's free. it will connect to your mysql server (and anything else). it lets you do point and click queries, so you can very quickly experiment to get the results that you want. it also builds a sql statement in case you need it. it installs in novice mode, so change it to the proficient level to do the functions. fair warning: at the proficient level, it can do complex queries, so pay attention to your logic. OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - 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: Help with Sum(), newbie
Thanks for the replies, Lucas, Terence, and John! John: I don't run Windows, so that will not help. Terence: No. I wanted the sums of all of the firsts...Lucas nailed it, but... Lucas: If I enter SELECT SUM(first) FROM example; it gives me the correct answer, 3. I created that simple database because I could not get it to work on the real one. I found a place online that said to use the command you said to use, but when I use it on my real data, it gives me an error message: ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause ..which is why I appended the GROUP BY to the example. My original statement for the real data looked something like this: SELECT column5, SUM(column5) FROM realdata WHERE processdate 2002-8-31 AND column3 = done AND url REGEXP mysql; Thus, I get the 1140 error. So, I add the GROUP BY and I get the same kind of printout I get in my example database, i.e., not a sum of all of the firsts. What I do want is a sum of all of the numbers in column 5 where the date is after a certain date, column 3 has done in it, and the url matches a regular expression mysql. How do I do that? Thanks! --kevin ***MY ORIGINAL EMAIL FOLLOWS*** mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? - 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: Help with Sum(), newbie
select sum(first) from example; should work. Artem -Original Message- From: Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 10:36 PM To: [EMAIL PROTECTED] Subject: Help with Sum(), newbie OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - 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 Attention: Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies. - 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: Help with Sum(), newbie
On 10 Oct 2002, at 11:45, Kevin wrote: SELECT column5, SUM(column5) FROM realdata WHERE processdate 2002-8-31 AND column3 = done AND url REGEXP mysql; Thus, I get the 1140 error. That's right. You can't get the individual values and the sum at the same time. The sum is a single value for the whole table, while there's a different individual value for each row, so they two things don't fit into the same result set. Leave column5, out of your query. If you need the individual values, get them in a separate query, without the sum. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Help with Sum(), newbie-- solved
Thanks, Keith! That does what I want. I followed in the manual what I thought would work (page 163, 164). And, since the response from my entering in a query was always hey stupid, you have to use the 'GROUP BY' when you do this, I never tried it without it. Anyway, that is just justification for what I was doing, and does not help anything except my self-esteem... Thanks again! - 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: Help with Sum(), newbie
snip not a sum of all of the firsts. What I do want is a sum of all of the numbers in column 5 where the date is after a certain date, column 3 has done in it, and the url matches a regular expression mysql. How do I do that? SELECT SUM(column5) FROM readdata WHERE processdate '2002-8-31' AND column3 = 'done' AND url REGEXP 'mysql'; Don't include column5 in your query. SUM() summarises a group of data by adding up its totals. If you include column5 outside SUM() in your query you are breaking up your results into one entry for each entry that matches the conditions of the WHERE statement. SUM is still doing its job correctly, but will only be adding together 1 number, which is why SUM(column5) = column5. Does that make sense?? L. - 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
Help with Sum(), newbie
OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - 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: Help with Sum(), newbie
well the result is correct. the sum of first is 1 for the first record. the sum of first AND second would be 3. select first, second, sum(first + second) from example; is that what you want? Rgds Terence - Original Message - From: Kevin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 10, 2002 10:36 AM Subject: Help with Sum(), newbie OK. Maybe I'm expecting too much of myself, but I can't figure out what I am doing wrong here: mysql select * from example; +---++ | first | second | +---++ | 1 | 2 | | 2 | 3 | +---++ 2 rows in set (0.00 sec) mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? Thanks! --kevin - 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: Help with Sum(), newbie
snip mysql select first, second, SUM(first) from example group by first; +---+++ | first | second | SUM(first) | +---+++ | 1 | 2 | 1 | | 2 | 3 | 2 | +---+++ 2 rows in set (0.00 sec) If it is not obvious, I want the sum of first, which, by my calculations, should be 3. What am I doing wrong? snip If you would like the result of the query to be the sums of the columns try this: SELECT SUM(first), SUM(second) FROM example; Or alternatively, just the sum from the first column use SELECT SUM(first) FROM example; - 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
