general math question 1

[captlid]

(max(col1-col2)) *0.5 - (col1-col2) * 0.5
Forgive me if I am wrong
(max(4-2)) * 0.5 = 1
(2-4) * 0.5 = -1
and 1 - (-1) = 2 mathematically
So why are my results coming out to zero?

 

[r937]

which of the following do you really want --

(max(col1-col2)) *0.5 - (col1-col2) * 0.5
(max(col1-col2)) *0.5 - (col2-col1) * 0.5



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

the first one

 

[TonyGroves]

As Rudy pointed out, first you're saying col1=4 and col2=2, then you're saying col1=2 and col2=4.

But in any event, it doesn't matter. max(col1-col2) means "the maximum of all values of col1-col2", which bears no relation to the current values of col1 and col2.

 

[captlid]

I put that in as a sample
what if the values were
max(col1-col2)*0.5 4-2=2
col1-col2 1-5=-4

2-(-4)=6

I am still getting zero as a result, thanks

 

[r937]

you are still getting zero as a result of what?

could you show your exact query please?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

SELECT col1, col2, (max(col1-col2))*0.5-(col1-col2)*0.5 as gb, ( col1 / ( col1 + col2 ) ) AS pct FROM temptable group by col3

 

[captlid]

I even tried doing max(col1)-min(col2) *0.5 and I still get zero from the calculation

 

[r937]

max(col1-col2) is not always equal to max(col1)-max(col2)

can you also please give a few rows of sample data

your group by is invalid, by the way

well, it's not invalid in mysql, but it's invalid in any other relational database, and in mysql, it could produce unpredictable results -- see 12.9.3 GROUP BY with Hidden Fields

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

col1 col2 col3 gb
4 2 blah1 (should equal 0)
3 3 blah2 (should equal 1)
2 4 blah3 (should equal 2)
1 5 blah4 (should equal 3)

 

[captlid]

What exactly is the group by clause for besides splitting results into rows? For examples, total sales per day...

 

[r937]

yes, the GROUP BY partitions the rows into groups

but then you must apply aggregate functions to those groups, not select individual columns -- unless they are in the GROUP BY

you just cannot (except in mysql, where you can get unpredictable results) select a column that isn't in the GROUP BY

anyhow, getting back to your problem...

based on your data, i ran this query --

SELECT col1
     , col2
     , col1-col2
     , max(col1-col2)
  FROM captlid 
group by col3

col1 col2 col1-col2 max(col1-col2)
 4    2       2          2
 3    3       0          0
 2    4      -2         -2
 1    5      -4         -4

so far, so good -- there is one row per col3 group

you know, something just occured to me

are you sure you want to use the MAX function and not the GREATEST function instead?

are you sure you need to use GROUP BY?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

I ran that same query to test if the max function works, mysql insisted on a group by clause. How's greatest() different from max().

My objective is to find the largest differential between (col1-col2) *0.5 and then subtract (col1-col2) *0.5

 

[r937]

How's greatest() different from max().

okay, i see you do really want MAX, not GREATEST, so i'll leave it to you to look them up in the manual

My objective is to find the largest differential between (col1-col2) *0.5 and then subtract (col1-col2) *0.5

okay, then this is what you want --

SELECT col1
     , col2
     , ( select max(col1-col2)
           from captlid )* 0.5  as m
     , (col1-col2) * 0.5        as d
     , ( select max(col1-col2)
           from captlid )* 0.5
      -(col1-col2) * 0.5        as diff
  FROM captlid

which gives these results --

col1 col2   m     d   diff
 4    2    1.0   1.0   .0
 3    3    1.0    .0  1.0
 2    4    1.0  -1.0  2.0
 1    5    1.0  -2.0  3.0

notice that there is no GROUP BY here, and notice in particular that the value m is the same from row to row, i.e. it is the same for the entire result set


before, when you were grouping on col3, you were getting a different value for max(col1-col2) for every col3

... which answers the question about why you were getting 0 for every row



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

Can I use this query in mysql version 4 ? (something tells me no )

 

[r937]

sorry, no

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[captlid]

ok thanks very much for the help, I'll do it programmatically for now, until the hosting providers of the world decide to upgrade their versions of mysql.

Thanks,

 

[r937]

programmatically? just run this query first --

select max(col1-col2)
  from captlid

this will return a single value, let's call it foo, which you can then just plug into this query --

SELECT col1
     , col2
     , ( [i]foo[/i] )* 0.5
      -(col1-col2) * 0.5        as diff
  FROM captlid

again, no GROUP BY




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)