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,
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
- Original Message -
From: Jake Peavy [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Saturday, April 05, 2008 11:59 AM
Subject: Re: Incorrect results from sum
On 4/5/08, Jonathan Mangin [EMAIL PROTECTED] wrote:
I'm getting incorrect results
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)
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: mysql@lists.mysql.com
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
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:
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
- Original Message -
From: George Law [EMAIL PROTECTED]
To: MYSQL General List mysql@lists.mysql.com
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
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. I've
- Original Message -
From: gerald_clark [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
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
- Original Message -
From: Ferindo Middleton Jr [EMAIL PROTECTED]
To: Ferindo Middleton Jr [EMAIL PROTECTED]
Cc: Hank [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Friday, March 31, 2006 7:30 PM
Subject: Re: TIMESTAMP field not automatically updating last_updated field
Ferindo
- Original Message -
From: Douglas S. Davis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
- Original Message -
From: Douglas S. Davis [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
- Original Message -
From: bob pilly [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
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
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: mysql@lists.mysql.com
Sent: Tuesday, January 24, 2006 4:43
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
- 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 [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday
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
(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, other_cols
from table1
where date between
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 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) =
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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 index
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
Stupid question. units is no different from item1 at this point.
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, November 15, 2005 2:36 PM
Subject: Missing column in select??
I thought I'd found a mistake...
$sql
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: [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
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
PM:
I have two tables
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):
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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 = t1
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
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
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 day
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 mysql@lists.mysql.com
Sent: Saturday, June 25, 2005 2:08 PM
Subject: Re: starting mysql 4.1.12 on Ubuntu Linux
hello, Hassan
which mysql gives me
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) {
@araylist
- Original Message -
From: Eamon Daly [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert
my $sql = sprintf 'EOF', join(',', @array);
SELECT col2, col3, col4
FROM table1
WHERE col1
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.
Happy
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 =
- Original Message -
From: Jonathan Mangin [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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 @array
- Original Message -
From: [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
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 would like to select several rows
man pkgadd
-J
- Original Message -
From: Joerg Bruehe [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
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: 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 into
- 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]
INSERT INTO `table2` (`columnnames`)
SELECT `columnnames
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 when 0 are needed
Try this:
my $sql = LOCK
]
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 write, TBREL write, TBACCESSLOG write;
my $sth = $dbh-prepare
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 needed ...
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 the
- 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/mysql
040913
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]
Sent: Wednesday, September 15, 2004 9:57 AM
Subject: Re
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
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,
i'm using
- 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
this data
54 matches
Mail list logo
