Function Question
My issue is actually in Excel. I'm hoping someone could help me... I need to total the values in column B for Emily. Is there a way other than =SUM (B1+B2+B4+B7)? -- Emily | 1 - Emily | 5 - Greg | 2 - Bob | 7 - Emily | 4 - Jenn | 2 - Greg | 1 - Emily | 7 - Bob | 3 - Emily | 3 - Nick Moreno|Communications Project Specialist|Home Federal Savings Bank 1016 Civic Center Drive NW|Rochester MN, 55901|Work 651-405-2010|Cell 612-987-0584 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Function Question
Have you tried: select UserName, Sum(ColB) from Table group by UserName; or select UserName, Sum(ColB) from Table group by UserName where UserName=Emily; Mike At 11:43 AM 1/12/2011, Nicholas Moreno wrote: My issue is actually in Excel. I'm hoping someone could help me... I need to total the values in column B for Emily. Is there a way other than =SUM (B1+B2+B4+B7)? -- Emily | 1 - Emily | 5 - Greg | 2 - Bob | 7 - Emily | 4 - Jenn | 2 - Greg | 1 - Emily | 7 - Bob | 3 - Emily | 3 - Nick Moreno|Communications Project Specialist|Home Federal Savings Bank 1016 Civic Center Drive NW|Rochester MN, 55901|Work 651-405-2010|Cell 612-987-0584 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Stored Procedure/Function Question
Hi there, I have a WEIRD question, that I can't find an answer too... Here is my stored function: DELIMITER $$ USE `mydatabase`$$ DROP FUNCTION IF EXISTS `SPLIT_STR`$$ CREATE definer=`thisus...@`%` FUNCTION `SPLIT_STR`( X VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) CHARSET latin1 DETERMINISTIC RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(X, delim, pos), LENGTH(SUBSTRING_INDEX(X, delim, pos -1)) + 1), delim, '')$$ DELIMITER ; Basically, as it sits, only the user 'thisuser' at any location can use this function, but I want to be able to allow ALL the users of this database access to it, as well, if I were to change this function, i have to go in, and manage every user that would be attached to it, to allow to use it again. I've tried '%'@'%', and I get the error that this user does not exist. Any help? Is it possible? Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
one-liner perfectionist function question
DROP FUNCTION IF EXISTS secs_to_hrs;
DELIMITER |
create function secs_to_hrs (secs varchar(10))
returns varchar(13)
DETERMINISTIC
BEGIN
return CONCAT_WS(' h ',lpad(secs/60 div 60,3,' ') ,
CONCAT(lpad(round(secs/60 mod 60),2,' '),' mins') );
END;
|
DELIMITER ;
What if I wanted to not display mins if they're 0?
In oracle I could possibly stick a decode in there.
I can probably break up the function into a few lines and make it work,
but curious if others had a one-liner addition that I could do.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date function question
Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
# of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date function question
There is also a LAST_DAY() function that returns the last day of the month: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Peter Brawley wrote: # of days since the first of the month from last month datediff(now(),date(concat(period_add(date_format(now(),'%Y%m'),-1),'01'))) the # of days since the end of last month (e.g. from 5/31/07) datediff(now(),date_sub(concat(date_format(now(),'%Y-%m-'),'01'),INTERVAL 1 DAY)) PB - Andrey Dmitriev wrote: Can someone advise the best way to determine a) the # of days since the first of the month from last month (e.g. from 5/1/07) b) the # of days since the end of last month (e.g. from 5/31/07) Is there are a good way to determine the # of workdays in a month. Thanks, Andrey -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
function question
Hi, I'm just wondering is there a function instead of update to overwrite data that has expired?? Or can mysql automatically deletes records or rows that has expires just like cookies delete themselves after expiring. Thanx for the info Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: function question
At 18:23 +0200 6/22/03, Anthony W. wrote: Hi, I'm just wondering is there a function instead of update to overwrite data that has expired?? Or can mysql automatically deletes records or rows that has expires just like cookies delete themselves after expiring. Cookies don't delete themselves after expiring. Something (e.g., a browser) notices that they've reached their expiration time and deletes them. Rows don't automatically expire in MySQL. You run a query to identify expired rows and delete them. Depending on your operating system, you might be able to schedule a job that runs periodically to perform this task, which is in most respects what you appear to want. For example, on Unix you can schedule a cron job. Thanx for the info Anthony -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE function question
I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE function question
I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATE function question
I believe it's day without the s. Not sure if it makes a difference, but that's how it's listed in the manual. date_add([thedatefield],Interval 15 day) Mark -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:35 AM To: [EMAIL PROTECTED] Subject: Re: DATE function question I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE function question
Yup I think that's write. I had a feeling I had something small wrong :) On Wed, 26 Mar 2003 13:50:12 -0500 Mark Armendariz [EMAIL PROTECTED] wrote: I believe it's day without the s. Not sure if it makes a difference, but that's how it's listed in the manual. date_add([thedatefield],Interval 15 day) Mark -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:35 AM To: [EMAIL PROTECTED] Subject: Re: DATE function question I believe the following will work: date_add([thedatefield],Interval 15 days) I am almost sure I got the syntax right off the top of my head. Check mysql.com for date functions if the above doesn't work. Serge. On Wed, 26 Mar 2003 10:16:18 -0500 Tom Ray [EMAIL PROTECTED] wrote: I have a small question about the DATE function. This is what I want to do: - User enters information into form and submits it to database - When info is added to database the current date is stored ala 2003-03-26 - Now I want to take that date 2003-03-26 and tack on either 15 or 30 days on to it so the date in another column reads either 2003-04-10 or 2003-04-25 I just can't seem to figure out how to do that math right so I get the result I'm looking for. Any thoughts or ideas? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum function question
Hi, I have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Thanks in advance for any advice that you might have. Javier Diaz IT Developer - Scanned for all known viruses by Messagelabs -- - 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: Sum function question
Hi Javier, Hi, I have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Relational queries are performed on tables. The result of such a query will also be a table. AFAIK there is no serialisation facility - you would need to post-process using another tool/language. Regards, =dn - 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
Sum function question
Hi, i have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Thanks in advance for any advice that you might have. Javier - Scanned for all known viruses by Messagelabs -- - 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
Sum function question
- Original Message - From: Javier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 20, 2002 2:47 PM Subject: Sum function question Hi, i have a table like this Key Field-1 A string1 A string2 A string3 B string1 B string 2 I want to create a query to get the following result (only one line by key field): A - string1,string2,string3 B - string1,string2 I try to use the Sum function but it only works on numbers, ( the number of occurrences of key field, is unknown) Thanks in advance for any advice that you might have. Javier - Scanned for all known viruses by Messagelabs -- - 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
