Trying to divide 2 count in SQL-CodePudding

I've been stuck for the past 3 hours on a single query. I have to divide the 2 counts below :

--Count the number of event hosted by each friend
SELECT friend_ID, COUNT(*) as TotalNumberOfEventHosted
FROM EVENT_
GROUP BY friend_id;

--Count the number of confirmed event
SELECT friend_ID, COUNT(*) as NbrOfConfirmedEvent
FROM EVENT_
NATURAL JOIN PROPOSED_DATE
WHERE IS_CONFIRMED = TRUE
GROUP BY FRIEND_ID ;

Here are the respective screenshot :

However, I'm unable to make a division, because I'm getting the following error : Subquery returns more than 1 row.

The goal at the end is to get the event acceptance rate (number of validated events out of the total) for each organizer. So to divide theNbrOfConfirmedEvent by the TotalNumberOfEventHosted.

And I wasn't able to combine both query. Does anyone have an idea ?

CodePudding user response：

you could try using a left join on the two subquery 

    select a.friend_ID, a.TotalNumberOfEventHosted/b.NbrOfConfirmedEvent
    from  ( 
    SELECT friend_ID, COUNT(*) as TotalNumberOfEventHosted
    FROM EVENT_
    GROUP BY friend_id) a 
    left join ( 
        SELECT friend_ID, COUNT(*) as NbrOfConfirmedEvent
        FROM EVENT_
        NATURAL JOIN PROPOSED_DATE
        WHERE IS_CONFIRMED = TRUE
        GROUP BY FRIEND_ID 
    ) b on a.friend_ID = b.friend_ID;

CodePudding user response：

Try putting those 2 queries in CTEs, joining them together in your main query and doing the division there

CodePudding user response：

What are you trying to do? have a table with three columns one with friend_ID, other with hosted events and a third with confirmed events? If that is You are trying to do just add a column with a CountIF() or SumIF() checking is the event is confirmed