472,146 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,146 software developers and data experts.

multiple select statements in one sql query and divsion

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?
Mar 12 '10 #1
3 42776
5,058 Expert 4TB

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.
Mar 12 '10 #2
mmm, I wrote the following SQL statement, but it doesn't work...any ideas?
Expand|Select|Wrap|Line Numbers
  1. SELECT(SELECT Count(*)
  2. FROM [Location Data]
  3. 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))
  4. GROUP BY [Location Data].park)
  5. /
  6. (SELECT Count(*) AS Expr1
  7. FROM [Location Data]
  8. 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"))
  9. GROUP BY [Location Data].park) AS Result
Mar 12 '10 #3
5,058 Expert 4TB
Try structuring it more like this:
Expand|Select|Wrap|Line Numbers
  2.     `q1`.`count` / `q2`.`count` AS 'Result'
  3. FROM (
  4.     SELECT  [Location Data].park, Count(*)
  5.     FROM    [Location Data] 
  6.     WHERE   ...
  7.     GROUP BY [Location Data].park
  8. ) AS `q1`
  10.     SELECT  [Location Data].park, Count(*)
  11.     FROM    [Location Data] 
  12.     WHERE   ...
  13.     GROUP BY [Location Data].park
  14. ) AS `q2`
  15. ON `q1`.`park` = `q2`.`park`
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.
Mar 12 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Joe via DotNetMonster.com | last post: by
2 posts views Thread by =?Utf-8?B?VGVycnk=?= | last post: by
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.