Can I calculate percentages between two subqueries in addExpression()?

TIBlock

10/7/23, 5:56 PM

I am trying to calculate the results of two COUNT() subqueries in order to produce a completion percentage. In this case I am getting the total number of courses assigned to an entity then comparing that entity with the number of courses completed by a user, then I would like to get the total percentage of courses completed by the user.. I am able to get the count of courses from the first entity, and the number of completed courses from the second, but cannot find a way of putting them together. I have found examples online of how one would calculate a percentage in the SELECT of a mySQL query, but am unsure if it is possible to do the same thing in Drupal. Any opinion or advice is appreciated.

This is an example of the working mySQL query I am trying to "Drupalize".


SELECT *,
        user_completed_courses_count * 100.00 / total_courses_count total
FROM (
    SELECT program_data.*,
        (SELECT COUNT(*)
        FROM user_program__field_user user_program
        LEFT JOIN user_program__field_courses_completed  user_prog_courses_completed
            ON user_prog_courses_completed.entity_id = user_program.entity_id
        WHERE user_program.field_user_target_id = $USER_ID***inserted UId*** ) user_completed_courses_count,
        (SELECT COUNT(*)
        FROM program_field_data program_data
        LEFT JOIN learning_path__field_courses  lp_courses
            ON program_data.id = lp_courses.entity_id
        LEFT JOIN course_entity__field_archive course_archive
            ON program_courses.field_courses_target_id = course_archive.entity_id lp_courses_count
    FROM program_field_data program_data
) q

0 + 0

database

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Can I calculate percentages between two subqueries in addExpression()?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.