Operations on Columns in MySql

[kreut]

Hello!

 

I was wondering if it's possible to do mathematical operations on entire columns using Sql. 

 

The idea:

 

SELECT COUNT1 FROM Table A, COUNT2 FROM Table B, return COUNT1/COUNT2

 

Basically I'd like to create an array of "COUNT1/COUNT2" before going back to the world of PHP.

 

Thank you,

 

Eric

[premiso]

Arrays are not a MySQL thing. They are done on the PHP side of things. You will want to use UNION.

 

$query = 'SELECT count(*) FROM table_a 
UNION 
SELECT count(*) FROM table_b;'

$query = mysql_query($query) or trigger_error('SQL Failed: ' . mysql_error());

if (is_resource($query))
    $rows = array();
    while ($row = mysql_fetch_row($query)) {
        $rows[] = $row[0];
    }    

    // index 0 will be table a  index 1 will be table b:
    echo 'Table A count is: ' . $rows[0] . ' and Table b's count is: ' . $rows[1];
}

[PFMaBiSmAd]

What relationship exists between the rows in the two tables? You would generally join the two tables using that relationship, which would let you use SELECT COUNT1/COUNT2 as ratio and you would retrieve the ratio value along with all the other values when you iterate over the rows from the query.

[kreut]

Thanks for the quick response!  I've played around with it a bit, but I fear that my syntax still isn't correct.  Basically, I'm looking to count "incorrectly submitted homework answers" from a class at the question level, and then count the number of total submitted answers at the question level.  Dividing the two at the question level will give me a summary of results for a particular class.  This is what I have so far:

 

SELECT questions.question_id, wrong_answers.COUNT(*) AS number_incorrect, total_submitted.COUNT(*) AS number_submitted, (number_incorrect/number_submitted) AS Ratio  //using the counts from below, compute the ratio of wrong answers
		FROM questions AS wrong_answers
		INNER JOIN
		submitted_homework
                        ON questions.question_id = submitted_homework.question_id
		WHERE ((questions.solution <> submitted_homework.submitted_solution)
                        AND submitted_homework.assignment_id = 15)
                        GROUP by questions.question_id ///get all of the wrongly submitted answers
		INNER JOIN
		submitted_homework AS total_submitted
		WHERE  assignment_id =15  //get the total number of submitted questions
                        GROUP by total_submitted.question_id

 

However, I fear, that even line 1 gives by an error.  Any additional advice would be appreciated.

[fenway]

What error?

[kreut]

Somehow it didn't like "wrong_answers.COUNT(*)"....however, in the interim I solved the problem with a less clean approach.  Basically, I used SQL to grab off records which showed me question id's, solutions, and submitted solutions.  I then used PHP to run through the array, group by question id, and compute the ratios.  It would have been nice to solve this all with SQL (I do think that it was possible!)...but with other fish to fry, I'd like to consider this post close. 

 

Thanks to all who responded.

 

-Eric

[fenway]

Oh, missed that -- you can't table prefix a COUNT().