CODEWITHSUNDEEP
mysqlsql
Oliver Dixon
Oliver Dixon

Reputation: 7405

Division by a second query

I'm trying to first query by the second one, how would I go about this?

SELECT COUNT(*) AS Result
FROM pparsdb.application
    INNER JOIN pparsdb.planning_scheme
    ON application.planning_scheme = planning_scheme.ps_code
    WHERE planning_scheme.markus_ra = 'GA'

SELECT Result / COUNT(*)
FROM pparsdb.planning_scheme 
WHERE markus_ra = 'GA'

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Assuming that you have only one planning scheme per application, here is an approach that uses just one query with no subqueries:

SELECT COUNT(a.planning_scheme) / COUNT(*) AS Result
FROM pparsdb.planning_scheme ps left outer join
     pparsdb.application a
     ON a.planning_scheme = ps.ps_code
WHERE ps.markus_ra = 'GA'

Even if that condition doesn't hold, you can do:

SELECT COUNT(a.planning_scheme) / COUNT(distinct ps.planning_scheme) AS Result
FROM pparsdb.planning_scheme ps left outer join
     pparsdb.application a
     ON a.planning_scheme = ps.ps_code
WHERE ps.markus_ra = 'GA'

Upvotes: 2

John Woo
John Woo

Reputation: 263693

try this,

SELECT  COUNT(*) / crss.totalCount AS Result
FROM    pparsdb.application
        INNER JOIN pparsdb.planning_scheme
            ON application.planning_scheme = planning_scheme.ps_code
        CROSS JOIN
        (
            SELECT  COUNT(*) totalCount
            FROM    pparsdb.planning_scheme 
            WHERE   markus_ra = 'GA'
        ) crss
WHERE   planning_scheme.markus_ra = 'GA'

Upvotes: 2

Related Questions