query

#1

I have a query that works right in Foxpro

Select Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) From invoiceitems, invoice Where
invoiceitems.siteid = invoice.siteid And invoiceitems.invoiceid =
invoice.id And (invoice.pending = false And invoice.islay = false)

In MySql it gets a huge negative number.

sprice*qantity gets 1209.88. share is a percentage. So the result of
the above query should be something less than 1209, but positive.

Can somebody give me an idea of how this should be done in mysql?

Noah Sombrero

#2

On 2010-10-21 02:20, Noah Sombrero wrote:

Quote:

Select Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) From invoiceitems, invoice Where
invoiceitems.siteid = invoice.siteid And invoiceitems.invoiceid =
invoice.id And (invoice.pending = false And invoice.islay = false)

What is the output of:

Select (invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share),
Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share),
count(*)
From invoiceitems, invoice
Where invoiceitems.siteid = invoice.siteid
And invoiceitems.invoiceid = invoice.id
And (invoice.pending = false
And invoice.islay = false)
group by (invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) with rollup

#3

On Thu, 21 Oct 2010 06:42:57 +0200, Lennart Jonsson
<erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

Quote:

On 2010-10-21 02:20, Noah Sombrero wrote:
Select Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) From invoiceitems, invoice Where
invoiceitems.siteid = invoice.siteid And invoiceitems.invoiceid =
invoice.id And (invoice.pending = false And invoice.islay = false)

What is the output of:

Select (invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share),
Sum(invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share),
count(*)
From invoiceitems, invoice
Where invoiceitems.siteid = invoice.siteid
And invoiceitems.invoiceid = invoice.id
And (invoice.pending = false
And invoice.islay = false)
group by (invoiceitems.sprice * invoiceitems.quantity *
invoiceitems.share) with rollup

about 150 items like this one

(invoiceitems.sprice * invoiceitems.quantity * invoiceitems.share)
-26994
Sum(invoiceitems.sprice * invoiceitems.quantity * invoiceitems.share)
-26994
count(*) 1

arranged in rows which I have folded here to fit within Agent's
margins.

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

Noah Sombrero

#4

Noah Sombrero:

Quote:

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM anyTable
WHERE hereYourCriteria

?

--
Erick

#5

Quote:

Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM anyTable
WHERE hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. I want the sum of those products including each row
selected. The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

#6

On Oct 21, 9:10*pm, Noah Sombrero <sombr... (AT) bluebottle (DOT) com> wrote:

Quote:

Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. *I need to compute the sum of the products of three columns
record by record. *Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM * anyTable
WHERE *hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. *I want the sum of those products including each row
selected. *The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

I don't understand.

Consider this dataset (apologies in advance for poor formatting):

Code:

SELECT * FROM xyz;
+------+------+------+
  Quote:
   
			
				x    | y    | z    |
+------+------+------+
2 |    3 | 0.50 |
3 |    5 | 0.50 |
2 |    5 | 0.50 |
5 |    2 | 0.50 |
5 |    3 | 0.50 |
+------+------+------+

Ignoring any WHERE conditions, what should the resultset look like (if
not like this...)?

Code:

SELECT SUM(x * y * z) FROM xyz;
+----------------+
  Quote:
   
			
				SUM(x * y * z) |
+----------------+
28.00 |
+----------------+

#7

On Thu, 21 Oct 2010 14:09:46 -0700 (PDT), strawberry
<zac.carey (AT) gmail (DOT) com> wrote:

Quote:

On Oct 21, 9:10*pm, Noah Sombrero <sombr... (AT) bluebottle (DOT) com> wrote:
Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. *I need to compute the sum of the products of three columns
record by record. *Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM * anyTable
WHERE *hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. *I want the sum of those products including each row
selected. *The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

I don't understand.

Consider this dataset (apologies in advance for poor formatting):

Code:

SELECT * FROM xyz;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    2 |    3 | 0.50 |
|    3 |    5 | 0.50 |
|    2 |    5 | 0.50 |
|    5 |    2 | 0.50 |
|    5 |    3 | 0.50 |
+------+------+------+

Ignoring any WHERE conditions, what should the resultset look like (if
not like this...)?

Code:

SELECT SUM(x * y * z) FROM xyz;
+----------------+
| SUM(x * y * z) |
+----------------+
|          28.00 |
+----------------+

Yes, that is what I want. The result I am getting is a massively
negative number.

Noah Sombrero

#8

On 10/21/2010 7:08 PM, Noah Sombrero wrote:

Quote:

On Thu, 21 Oct 2010 14:09:46 -0700 (PDT), strawberry
zac.carey (AT) gmail (DOT) com> wrote:

On Oct 21, 9:10 pm, Noah Sombrero<sombr... (AT) bluebottle (DOT) com> wrote:
Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM anyTable
WHERE hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. I want the sum of those products including each row
selected. The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

I don't understand.

Consider this dataset (apologies in advance for poor formatting):

Code:

SELECT * FROM xyz;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    2 |    3 | 0.50 |
|    3 |    5 | 0.50 |
|    2 |    5 | 0.50 |
|    5 |    2 | 0.50 |
|    5 |    3 | 0.50 |
+------+------+------+

Ignoring any WHERE conditions, what should the resultset look like (if
not like this...)?

Code:

SELECT SUM(x * y * z) FROM xyz;
+----------------+
| SUM(x * y * z) |
+----------------+
|          28.00 |
+----------------+

Yes, that is what I want. The result I am getting is a massively
negative number.

Noah Sombrero

What is this "massively negative number"? Are you exceeding the limits
of a signed integer on your system?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#9

On Thu, 21 Oct 2010 19:28:01 -0400, Jerry Stuckle
<jstucklex (AT) attglobal (DOT) net> wrote:

Quote:

On 10/21/2010 7:08 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 14:09:46 -0700 (PDT), strawberry
zac.carey (AT) gmail (DOT) com> wrote:

On Oct 21, 9:10 pm, Noah Sombrero<sombr... (AT) bluebottle (DOT) com> wrote:
Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM anyTable
WHERE hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. I want the sum of those products including each row
selected. The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

I don't understand.

Consider this dataset (apologies in advance for poor formatting):

Code:

SELECT * FROM xyz;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    2 |    3 | 0.50 |
|    3 |    5 | 0.50 |
|    2 |    5 | 0.50 |
|    5 |    2 | 0.50 |
|    5 |    3 | 0.50 |
+------+------+------+

Ignoring any WHERE conditions, what should the resultset look like (if
not like this...)?

Code:

SELECT SUM(x * y * z) FROM xyz;
+----------------+
| SUM(x * y * z) |
+----------------+
|          28.00 |
+----------------+

Yes, that is what I want. The result I am getting is a massively
negative number.

Noah Sombrero

What is this "massively negative number"? Are you exceeding the limits
of a signed integer on your system?

-725928

Using the first two columns the total is 1209.88. The third column is
a decimal, so the result for three columns should be between 0 and
1209.88.

Noah Sombrero

#10

On 10/21/2010 7:42 PM, Noah Sombrero wrote:

Quote:

On Thu, 21 Oct 2010 19:28:01 -0400, Jerry Stuckle
jstucklex (AT) attglobal (DOT) net> wrote:

On 10/21/2010 7:08 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 14:09:46 -0700 (PDT), strawberry
zac.carey (AT) gmail (DOT) com> wrote:

On Oct 21, 9:10 pm, Noah Sombrero<sombr... (AT) bluebottle (DOT) com> wrote:
Noah Sombrero:

One important thing I see here is that the sum command is having no
effect. I need to compute the sum of the products of three columns
record by record. Surely MySql can do that?

I sense a misunderstanding here.
SUM(), in MySQL, is a group function. Not a function that calculates
the sum of three fields in a row.

Do you want something like:

SELECT (fieldA + fieldB + fieldC) as 3fieldSum
FROM anyTable
WHERE hereYourCriteria

?

No, I am using asterices which should give me the product of the three
columns. I want the sum of those products including each row
selected. The result would give me a recordset with one row and one
column,with one number total of it all.

Noah Sombrero

I don't understand.

Consider this dataset (apologies in advance for poor formatting):

Code:

SELECT * FROM xyz;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    2 |    3 | 0.50 |
|    3 |    5 | 0.50 |
|    2 |    5 | 0.50 |
|    5 |    2 | 0.50 |
|    5 |    3 | 0.50 |
+------+------+------+

Ignoring any WHERE conditions, what should the resultset look like (if
not like this...)?

Code:

SELECT SUM(x * y * z) FROM xyz;
+----------------+
| SUM(x * y * z) |
+----------------+
|          28.00 |
+----------------+

Yes, that is what I want. The result I am getting is a massively
negative number.

Noah Sombrero

What is this "massively negative number"? Are you exceeding the limits
of a signed integer on your system?

-725928

Using the first two columns the total is 1209.88. The third column is
a decimal, so the result for three columns should be between 0 and
1209.88.

Noah Sombrero

Depending on the order of evaluation, yes. But you haven't given us the
actual table definitions and data you're using, so it's going to be hard
to determine the cause of your problem.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================