query

#21

On 10/21/2010 11:46 PM, Noah Sombrero wrote:

Quote:

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

On 10/21/2010 11:03 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 22:41:11 -0400, Jerry Stuckle
jstucklex (AT) attglobal (DOT) net> wrote:

On 10/21/2010 10:34 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 21:14:20 -0400, Jerry Stuckle
jstucklex (AT) attglobal (DOT) net> wrote:

On 10/21/2010 8:46 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 20:20:33 -0400, Jerry Stuckle
jstucklex (AT) attglobal (DOT) net> wrote:

On 10/21/2010 8:09 PM, Noah Sombrero wrote:
On Thu, 21 Oct 2010 20:00:13 -0400, Jerry Stuckle
jstucklex (AT) attglobal (DOT) net> wrote:

On 10/21/2010 7:42 PM, Noah Sombrero wrote:
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.

Order of evaluation shouldn't matter for a product of three numbers. I
was hoping somebody would look at my sql

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)

and say, no you can't get the result you want with that query (which
works in Foxpro). Do it this way in MySql...

And maybe something about Lennart's discover that

(invoiceitems.sprice * invoiceitems.quantity * invoiceitems.share)
-26994

gets the same result as

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

So the sum command has no effect here. So how should I sum a sequence
of columns in MySql?

Noah Sombrero

As I said - without your actual table definitions and data, its going to
be very difficult to help you. And order of evaluation ALWAYS can make
a difference.

In other words, there is nothing specific to MySql syntax that would
prevent my sql statement form working as I hoped?

Noah Sombrero

Without the table definitions and data you are using, it is going to be
very difficult to help you. That includes any statements as to whether
your syntax should work or not.

Without such information, anything is a guess. If you don't wish to
supply it, I suggest you hire a consultant to find out what your problem
is. And no, I am not available.

I wasn't looking for that kind of help. I don't need a consultant. I
need somebody who knows MySql well enough to know if I have violated
any obvious usage rules. I think you have answered my question in
spite of yourself.

I will explore the difference between the statement with sum and the
one without. Thanks for helping me see that Lennart.

Noah Sombrero

You are in need of a consultant. And no, I answered nothing, because I
don't have enough information to provide an answer.

And since you are unwilling to provide the information necessary to help
you, and are unwilling to hire a consultant, good luck.

If my degree in software engineering and years of experience aren't
enough to get me by, I'll need more than good luck. Yes, Lennart
provided the clue I needed. The situation is coming clear.

Noah Sombrero

Doesn't sound like it is enough - you had to ask for help here, didn't
you? Nothing wrong with that - everyone needs help once in a while;
others need help all the time.

Yes, and the patient has the right to decide what help to seek and
what therapy to accept.

And the smart accept what is recommended by the expert. The stupid do not.

Quote:

I suggest when you ask for help, you learn to take advice. I did, when
I started programming about 43 years ago.

I have been at it a while too.

Which I suspect means about 3 years, maybe 4.

Quote:

And I did get the help I needed.

Noah Sombrero

Not from here, you didn't. But I guess you aren't smart enough to
realize that yet.

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

#22

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

Quote:

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

Besdides the question from Lennart,
Which version of MySQL are you using?
(i hope its not a very old one?)

--
Luuk

#23

On Fri, 22 Oct 2010 09:06:41 +0200, Lennart Jonsson
<erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

Quote:

On 2010-10-21 20:27, Noah Sombrero wrote:
[...]

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

For one such row, what are the value of the individual columns. I.e.

select 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)
And (invoiceitems.sprice * invoiceitems.quantity * invoiceitems.share)
= -26994
limit 1;

/Lennart

Starting from your different way of looking at what I was doing, I
found that yes, invoiceitems.share can have negative numbers. I then
realized that what I needed to use was inventory.share with
appropriate id links. That gets the result I was looking for.

All I needed was somebody to show me that MySql sql is not so very
different from Foxpro sql, and to send me off looking in a new
direction. Thanks for doing that.

Noah Sombrero

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

query

comp.databases.mysql comp.databases.mysql

Re: query - 10-22-2010 , 07:09 AM

Re: query - 10-22-2010 , 01:28 PM

Re: query - 10-22-2010 , 09:39 PM