multiple select statements in one sql query and divsion

[els12]

How do I write 2 distinct select queries in one sql statement, and then also divide the results of one by the other?

The first query will give me a subset of all the records in the table...the second query will give me a second (larger subset)...and then I want to use those two results to come up with a percentage.

Any thoughts?

[Atli]

Hey.

You have three options.

1. Merge the two queries, using sub-queries and/or joins, so that they can both be executed as one query.
2. Fetch them both into your front-end application and deal with them there.
3. Fetch them both into a procedure and deal with them there.

It's usually easiest to just go with #2, but in case this needs to happen inside MySQL, I would try #1 first but if that fails, go with #3.

[els12]

mmm, I wrote the following SQL statement, but it doesn't work...any ideas?

SELECT(SELECT Count(*)
FROM [Location Data]
WHERE ((([Location Data].Status)="Oper/Obso" Or ([Location Data].Status)="excess" Or ([Location Data].Status)="operating" Or ([Location Data].Status)="inactive") AND (([Location Data].Asset_Code)<>"0000" And ([Location Data].Asset_Code)<>"9999" And ([Location Data].Asset_Code)<>"8999") AND (([Location Data].LOC_Qty)>0))
GROUP BY [Location Data].park)
/
(SELECT Count(*) AS Expr1
FROM [Location Data]
WHERE ((([Location Data].Status)="Oper/Obso" Or ([Location Data].Status)="excess" Or ([Location Data].Status)="operating" Or ([Location Data].Status)="inactive") AND (([Location Data].Asset_Code)<>"0000" And ([Location Data].Asset_Code)<>"9999" And ([Location Data].Asset_Code)<>"8999"))
GROUP BY [Location Data].park) AS Result

[Atli]

Try structuring it more like this:
[code=sql]SELECT
`q1`.`count` / `q2`.`count` AS 'Result'
FROM (
SELECT [Location Data].park, Count(*)
FROM [Location Data]
WHERE ...
GROUP BY [Location Data].park
) AS `q1`
INNER JOIN (
SELECT [Location Data].park, Count(*)
FROM [Location Data]
WHERE ...
GROUP BY [Location Data].park
) AS `q2`
ON `q1`.`park` = `q2`.`park`[/code]

There the result set of each statement is used as a table, who are then joined on the "park" field. That allows you to use the two values as if they were just sitting next to each other in a single table.