Sorry to return to this topic, I haven't found a lot to explain
what's happening.
I'm trying to total certain nutrients consumed on a given date
(though I've removed date temporarily).
You'll see I have three items (in two meals) in itemized,
and two meal totals in simple.
mysql> select id, ite
Hi,
Here's the basic query I'm trying. It's supposed to return
the totals of each nutrient (carb, in this case) by date.
The data totals 218.31, but I get 190.80.
select itemized.day_date as day_date,
round(sum(my_menu.carb * units) + simple.carb,2)
from itemized inner join simple using (uid) in
- Original Message -
From: "Jake Peavy" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Saturday, April 05, 2008 11:59 AM
Subject: Re: Incorrect results from sum
On 4/5/08, Jonathan Mangin <[EMAIL PROTECTED]> wrote:
I&
I'm getting incorrect results from a sum and wonder if
anyone sees something obviously wrong. (Won't surprise
me.) Leaving 'simple' out of the equation (or adding
'simple' values manually) gets me the correct number.
$menu is a personalized table of meal/recipe ingredients.
itemized is a list of
File downloads are enabled in IE but refuse to work.
Does anyone know where I can FTP the latest version
of mod_auth_mysql?
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
- Original Message -
From: "Daniel da Veiga" <[EMAIL PROTECTED]>
To:
Sent: Thursday, December 07, 2006 9:48 AM
Subject: Re: Renaming the root user - problems.
> On 12/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > my question refers to the user "root" in MySQL 5.
Hi,
I'm using DBI and a dsn with 'mysql_client_found_rows=0' appended.
A normal update returns 0E0 if no data has changed.
The update part of insert...on duplicate key update always returns
2, whether data has changed or otherwise, plus the timestamp
column is not automatically updated.
Is there
Section 3.6.2 of the 4.1 manual has this example
using a subselect:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
I use this basic syntax with max(date) alot.
- Original Message -
From: "João Cândido de Souza Neto" <[EMAIL PROTECTED]>
To:
Sent:
- Original Message -
From: "George Law" <[EMAIL PROTECTED]>
To: "MYSQL General List"
Sent: Wednesday, August 09, 2006 3:40 PM
Subject: forcing leading 0 for numeric fields
Hello All,
I have what is probably a simple question.
I have a table of phone numbers, broken into npa,nxx,stati
- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, May 16, 2006 3:08 PM
Subject: Re: Converting varchar field into primary key
> Jonathan Mangin wrote:
>
> >>>
> > I'm creating a new MySQL database from an existing Filemaker db.
> >
> > My problem is that some of the existing 'numbers' in one column (it
> > was a text field in FMP) have leading zeros. eg: 003, 0007, 012,
> > 001234. I need to maintain these numbers 'as is' - complete with
> > zeros
- Original Message -
From: "Ferindo Middleton Jr" <[EMAIL PROTECTED]>
To: "Ferindo Middleton Jr" <[EMAIL PROTECTED]>
Cc: "Hank" <[EMAIL PROTECTED]>;
Sent: Friday, March 31, 2006 7:30 PM
Subject: Re: TIMESTAMP field not automatically updating last_updated field
> Ferindo Middleton Jr wr
- Original Message -
From: "Douglas S. Davis" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the
- Original Message -
From: "Douglas S. Davis" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the
- Original Message -
From: "bob pilly" <[EMAIL PROTECTED]>
To:
Sent: Sunday, February 12, 2006 12:44 AM
Subject: Returning values from an INSERT
> Hi everyone, im new to SQL and have a question that someone can hopefully
answer
>
> If i am inserting a new record into a table that has
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = "insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on du
I got exactly that error message last night when doing a
numeric comparison on a varchar column. Oops.
Kind of misleading, though.
- Original Message -
From: "David Godsey" <[EMAIL PROTECTED]>
To: "George Law" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, January 24, 2006 4:43 PM
Subject: RE:
RE: Calendar table workaround>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.
I forget the query but I know it can be done. But can't you just have a table
(called calendar?) with each entry having it's own row with a date co
- Original Message -
From: "Rhino" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround
>
> - Original Message -
> From: "Jonathan Mangin
I created a calendar table (date only), but all
where clauses include a uid. Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?
my $sth = $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
select date,
? as uid
from calendar
where date betw
I'm trying to correct a situation I've created after
combining separate date/time columns into a datetime column.
Here are skeletons of new table definitions.
CREATE TABLE products
(id mediumint unsigned primary key not null auto_increment,
item varchar(40) not null,
priceA decimal(6,3),
(I used to have separate date/time cols. in all tables
but changed them to datetime and buggered up some stuff.
Now I'm trying to find the best way to fix this.)
If I have an indexed datetime column (`date`), and say:
select date,
from table1
where date between '2005-08-01' and '200
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Monday, November 21, 2005 8:48 AM
Subject: Index of JOINed table?
I have a simple query, (part of a union).
calendar is a table of only dates, `date` as PK.
sales has a multi-col ind
I have a simple query, (part of a union).
calendar is a table of only dates, `date` as PK.
sales has a multi-col index on (date, uid).
(select calendar.date as date,
time_format(time(sales.date),'%H:%i') as time,
from calendar
left join sales
on date(sales.date) = calendar.d
Stupid question. units is no different from item1 at this point.
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, November 15, 2005 2:36 PM
Subject: Missing column in select??
I thought I'd found a mistake...
$sql
I thought I'd found a mistake...
$sql = "(select date(date) as date,
time_format(time(date),'%H:%i') as time,
units, # I forgot to include this
round(sum(item1 * units),2),
but all the numbers are correct. Does mysql know to multiply b
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, October 07, 2005 2:09 PM
Subject: Re: Joining tables, duplicating none
"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 10/07/2005 02:57:28
I have two tables with date and uid cols. in common.
Table 1 has one row per date, Table 2 has a maximum
of 7 rows per date.
select t1.date, t1.val, t2.val from t1
right join t2 on t1.date = t2.date
where t1.date between '2005-08-01' and '2005-08-14'
and t1.uid = 'me';
+-
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Saturday, September 24, 2005 2:18 PM
Subject: Using ifnull in a subquery
Hello all,
This works if t2 is populated:
select t1.item_no, t1.value1 *
(select weight from t2 where
item_no
Hello all,
This works if t2 is populated:
select t1.item_no, t1.value1 *
(select weight from t2 where
item_no = t1.item_no and
descrip = 'dime bag')
from t1
where t1.descrip = 'marigold seeds'
If t1.item_no and/or 'dime bag' don't exist in t2 I'd
like to multiply by a different value (5):
sele
I see these really need to be int or time types.
Is there no way they can default to NULL or blank?
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
mysql> select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row in se
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
Is this string arithmetic? Can/should I do this?
(I see the second one won't work without single-quotes.)
mysql> select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row
Jonathan Mangin wrote:
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
But each test is performed no more than once per d
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
select test1.date, test1.time, test2.date, test2.time from
test1 left join test2 on
- Original Message -
From: "Juan Pedro Reyes Molina" <[EMAIL PROTECTED]>
To: "Hassan Schroeder" <[EMAIL PROTECTED]>
Cc: "mysql general list"
Sent: Saturday, June 25, 2005 2:08 PM
Subject: Re: starting mysql 4.1.12 on Ubuntu Linux
hello, Hassan
which mysql gives me /usr/bin/mysql. e
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = "INSERT into $table2"; # dynamic name with $user_id as root
$sql .= sprintf <<'EOF', join(',', @array);
I see.
I suppose this produced quite a few grins.
Ha
- Original Message -
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert
my $sql = sprintf <<'EOF', join(',', @array);
> This would be even faster if you could concatenate all of the elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @ar
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, April 26, 2005 3:20 PM
Subject: Re: Efficient select/insert
"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 04/26/2005 12:26:20
PM:
I woul
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, April 26, 2005 11:26 AM
Subject: Efficient select/insert
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @a
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q->param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = "select col2, col3, col4 from table1
where col1 = ?";
my $sth = $dbh->prepare(
man pkgadd
-J
- Original Message -
From: "Joerg Bruehe" <[EMAIL PROTECTED]>
To:
Cc: "Jonathan Stockley" <[EMAIL PROTECTED]>
Sent: Thursday, March 17, 2005 9:16 AM
Subject: Re: 4.1.10a packaging for Solaris
Hi Jonathan, all!
Am Mi, den 16.03.2005 schrieb Jonathan Stockley um 22:33:
I jus
Hello,
Does this mean a key is not being used?
mysql> explain select vl_ts from view_log where vl_uid='bb';
+--+--+---+--+-+--+--++
| table| type | possible_keys | key | key_len | ref | rows | Extra
|
+--+--+
- Original Message -
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 9:19 AM
Subject: RE: copy data only from one table to another table
[snip]
INSER
- Original Message -
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "Chip Wiegand" <[EMAIL PROTECTED]>; "MySQL List"
<[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 8:10 AM
Subject: RE: copy data only from one table to another table
[snip]
How do I copy all data only from one table
Wexler" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Sunday, October 24, 2004 4:53 PM
Subject: Re: Cannot lock: called with 1 bind variables when 0 are needed
Try this:
my $sql = "LOCK tables TBUSR write, TBAUTH write, TBDATALOG write,
TBAGCY
Hmmm, you're probably right. I got carried away.
- Original Message -
From: "Mike Wexler" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Sunday, October 24, 2004 4:53 PM
Subject: Re: Cannot lock: called with 1 bind variables
Hi all,
With 3.23.38 I have:
my $sql = "LOCK tables TBUSR write, TBAUTH write, TBDATALOG write,
TBAGCY write, TBREL write, TBACCESSLOG write";
my $sth = $dbh->prepare($sql);
$sth->execute($sql) || die "Cannot lock: " . $sth->errstr();
I get:
Cannot lock: called with 1 bind variables when 0 are need
Mine is owned by root:root and has its sticky bit set.
Does not allow a normal user to delete.
- Original Message -
From: "Annie Xie" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
- Original Message -
From: "æé" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Wednesday, September 15, 2004 9:25 AM
Subject: mysql still can't start up
> when I used the command "safe_mysqld start" the system told me "
> Starting mysqld daemon with databases from /var/lib/my
> >
> > I have tried different URL's, including trying the ftp URL at Oakland,
> > USA, and the Australian URL previously cited. I get the same problem -
> > instead of getting a dialogue box giving me the option of saving the
> > file to disk (and, in what path), the browser commences downloading
I didn't actually count the characters but it looks very close to a
512-character limit. (I used a pica stick on the screen, very accurate!)
The error message appears to be showing a truncated line. Is possible?
--Jon
> > >
> > > INSERT INTO dirxml.emp
> > >
(fld_ind_id,fld_frname,fld_srname,fld_
If you've installed the jar in $JAVA_HOME/lib/ext it shouldn't
need to be referenced in $CLASSPATH.
- Original Message -
From: "Mahesh S" <[EMAIL PROTECTED]>
To: "my sql" <[EMAIL PROTECTED]>
Sent: Thursday, August 05, 2004 1:36 AM
Subject: unable to connect mysql and jsp
> hi all,
>
>
- Original Message -
From: "John Berman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, August 01, 2004 7:27 AM
Subject: Connectiing
> Hi
>
> Sorry if I'm off topic but I am a touch desperate
>
>
> We make use of a mysql 4.x hosted by a third party and we use ASP to
access
>
54 matches
Mail list logo
