derived results

[captlid]

say theres a table with 4 columns

col1 col2 col3 col4
val1 val2 4 3
val3 val1 5 4
val1 val4 6 2

I am trying to find out how many times value one is in one column based on a condition, and how many times its in the other column based on another condition. Unfortunately I cant use subqueries to do it cause of the hosting provider.
The below just sums the total times val1 appears multiplied by 2. Not really sure what I am doing wrong.

select count( col1 * ( col3 > col4 ) ) + ( col2 * ( col4 > col3 ) ) AS count FROM table WHERE col3 = 'val1' OR col4 = 'val1'

thanks

 

[ericbrunson]

Try something like:

select sum( case col3 > col4 then 1 else 0 ),
       sum( case col4 > col3 then 1 else 0 )
...

Read the manual for the specfics on CASE, it's very powerful.

With a "case" this simple you may also be able to use if() which would be found in the same manual section.

 

[captlid]

Thanks for the idea, I have heard of the case command I am still not sure what it does. I'll read the manual on it and get back to you if I am still having problems,

 

[captlid]

SELECT sum(
CASE col3 < col4
THEN 1
ELSE 0
END ) AS leftcol, sum(
CASE col3 > col4
THEN 1
ELSE 0
END ) AS rightcol
FROM table
WHERE col1 = 'val1' OR col2 = 'val1'

I tried the above it gives a syntax error, I added in the end still get syntax errors, mysql version < 4.1 Wehn I put in col1 instead of 1 in the the THEN CLAUSE it just adds up the total values of val1 in col1

 

[r937]

select sum( 
        case [b]when[/b] col3 < col4
             then 1 else 0 end 
          ) as leftcol
     , sum( 
        case [b]when[/b] col3 > col4
             then 1 else 0 end
          ) as rightcol
  from table
 where col1 = 'val1' 
    or col2 = 'val1'

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

 

[ericbrunson]

That's why I told you to read the manual.