calculations 1

[captlid]

count(case when col1 = list.value and col3 > col4 then col2 end) as calc1,
count(case when col1 = list.value and col3 < col4 then col2 end) as calc2 from calculations, list group by list.value

theres two tables one with a list of values of in it, and another with four columns (integer values) that need to be calculated.

How would multiple calculations be made using the results of calc1 and calc2? ie..
calc1/calc2, and/or calc1-calc2

regards,

 

[ericbrunson]

You would have to do the calculation again, unless you used some form of @ notation, which I abhor and avoid and therefore can't comment on.

 

[TonyGroves]

Or, you could use a sub-query:
[tt]
SELECT calc1/calc2, calc1-calc2
FROM
(
SELECT
COUNT(...) AS calc1,
COUNT(...) AS calc2
FROM calculations, list
WHERE ...
GROUP BY list.value
)
[/tt]

 

[TonyGroves]

Of course, the sub-query needs to be aliased:
[tt]
SELECT calc1/calc2, calc1-calc2
FROM
(
SELECT
COUNT(...) AS calc1,
COUNT(...) AS calc2
FROM calculations, list
WHERE ...
GROUP BY list.value
)
sq
[/tt]

 

[captlid]

thanks for the subquery, but what if the host doesnt have 4.1 yet?

 

[TonyGroves]

Then you're stuck. And no doubt Eric will be happy to hear I can't come up with an @variable solution either.

You could of course use a temporary table as a substitute for the sub-query, but it's probably not worth it.

 

[captlid]

I cant quite get the hang of temporary tables... In php do I then I have to use mysql_pquery() for the original result set?

thanks,

 

[captlid]

oops sorry I meant mysql_unbuffered_query()

 

[TonyGroves]

You don't have to use any special PHP code.

Your SQL might look like:[tt]
CREATE TEMPORARY TABLE tt AS
SELECT
COUNT(...) AS calc1,
COUNT(...) AS calc2
FROM calculations, list
WHERE ...
GROUP BY list.value;
SELECT calc1/calc2, calc1-calc2 FROM tt;
[/tt]
You can then process the output from the final query as if it was from a normal table. The temporary table will be automatically dropped at the end of your session.

 

[captlid]

I tried running that query in php and i get supplied arguement invalid mysql result resource;
It executes in phpmyadmin, but doesnt display the table

 

[TonyGroves]

That is of course two queries, which you might need to run separately.

On the other hand, you could just forget about the whole temporary tables idea, and use PHP to do the calc1/calc2 and calc1-calc2 calculations. It will probably be faster.

 

[captlid]

actually my current setup is using php to calculate those values, but I need to sort by them, thats why I asked if theres anyway to do the whole thing in mysql...

 

[TonyGroves]

OK. Which query is causing the error message, the first or second?

 

[captlid]

$query1 ="CREATE TEMPORARY TABLE tt AS
SELECT
COUNT(...) AS calc1,
COUNT(...) AS calc2
FROM calculations, list
GROUP BY list.value;";
$query2="SELECT calc1/calc2 as div, calc1-calc2 as sub FROM tt";
$result1= mysql_query($query1);
$result2= mysql_query($query2);

$result = $result1 . $result2;
while ($row = mysql_fetch_array($result))
{ echo $row[div] . $row<sub>; }

I am getting no error codes., just the below

0:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /dirtoapache/htdocs/file.php on line 13
Current News</sub>

 

[TonyGroves]

Query 1 does not produce a result set, so concatenating it with the result of Query 2 doesn't make sense. You should just read the result of Query2.

 

[captlid]

thank you so much for all your help,

I got it working finally