multiply SELECT results with values from another table

Question

I have 3 tables with student data from different schools. I am supposed to get the count of students per school in one table(done) and then using the given probabilities to figure out how many students per set of probabilities. I have the results of students per school from another query.

Ex: I used a query to find the total number of students at school "CAA". If it is 198, then the probability distribution will be (0.05*198, 0.08*198, 0.18*198, 0.3*198, 0.11*198, 0.28*198). How do I grab those values from said table and multiply with my results?

I hope this makes sense and forgive me I am new to Postgres. I have attached my query to get the count and photos of the two relevant tables.

simulated_records:

record_id(PK bigint)   Status(text)    grade(text
 1                     CL              -
 2                     CEC             -
 3                     CEC             -
 4                     CEC             -
 5                     CAA             -
 6                     CAS             -
 7                     CAA             -
 8                     CAA             -
 9                     CAA             -
 10                    CL              -

school_probs:

school_code(PK bigint)  school(text)    probs(numeric)
1                       CAA             {0.05,0.08,0.18,0.3,0.11,0.28}
2                       CAS             {0.06,0.1,0.295,0.36,0.12,0.065}
3                       CBA             {0.05,0.11,0.35,0.32,0.12,0.05}
4                       CL              {0.07,0.09,0.24,0.4,0.06,0.09}

grade_values:

id(PK integer)   score(text)    grade(text
1                95-100         A
2                90-94          A-
3                80-89          B+
4                70-79          B
5                60-69          C
6                0-59           D

MY QUERY:

SELECT simulated_records.school, COUNT(simulated_records.school) as CountSchool
FROM simulated_records, school_probs
WHERE simulated_records.school = school_probs.school
GROUP BY simulated_records.school;

What I should be able to do is populate grades in the simulated records table based on the amount of students per school and using the probabilities(which breaks down how many kids should have what grade using a function.

Also the actual simulated records table has over 2000 records.

Expected results: The empty grade column in simulated_records would be filled with grades based on the count of students and the probability from school_probs.

record_id(PK bigint)   Status(text)    grade(text
 1                     CL              A
 2                     CEC             B
 3                     CEC             B
 4                     CEC             A
 5                     CAA             C
 6                     CAS             B
 7                     CAA             D
 8                     CAA             A
 9                     CAA             A
 10                    CL              C

Answer 1

demo:db<>fiddle

SELECT 
    school,
    json_object_agg(gv.grade, s.students)                           -- 4
FROM (
    SELECT 
        s.school, 
        ceil(unnest(sp.probs) * s.students_per_school) students,    -- 2   
        generate_series(1,6) gs                                     -- 3
    FROM (
        SELECT 'CAA'::text as school, 198 as students_per_school    -- 1
    ) s
    JOIN school_probs sp ON s.school = sp.school                      
) s
JOIN grade_values gv ON gv.id = s.gs
GROUP BY school

Result:

school   json_object_agg
CAA      { "A" : 10, "A-" : 16, "B+" : 36, "B" : 60, "C" : 22, "D" : 56 }

Hoping this is the result you expect:

1. Getting your data from somewhere (simulate your table or subquery or whatever)
2. 1. unnest expands your numeric probs array into one row each element. This is the main trick
   2. multiply with your students
   3. ceil rounds up (otherwise you could do a floor for round down or round for normal rounding)
3. generate_series adds an integer column for the ability to join the grade_values table
4. Now you have one row for each grade per school (see Edit below). If this is your expected result, that's it. If you want to aggregate the grade values into one row per school you could do this json_object_agg

---

Edit: Version without aggregating:

SELECT 
    school,
    gv.grade, 
    s.students
FROM (
    SELECT 
        s.school, 
        ceil(unnest(sp.probs) * s.students_per_school) students, 
        generate_series(1,6) gs
    FROM (
        SELECT 'CAA'::text as school, 198 as students_per_school
    ) s
    JOIN school_probs sp ON s.school = sp.school
) s
JOIN grade_values gv ON gv.id = s.gs

Result:

school   grade   students
CAA      A       10
CAA      A-      16
CAA      B+      36
CAA      B       60
CAA      C       22
CAA      D       56

Answer 2

Not sure what you expect as a result, but here goes:

select school, factor, CountSchool
from (
      SELECT simulated_records.school, COUNT(simulated_records.school) as CountSchool
      FROM simulated_records, school_probs
      WHERE simulated_records.school = school_probs.school
      GROUP BY simulated_records.school
     ) eq
cross join (
    values 
         (0.05), (0.08), (0.18), (0.3), (0.11), (0.28)
    ) t (factor)

this would produce a result like this:

    school  factor  count_of
1   CAA 0.0500  198
2   CAA 0.0800  198
3   CAA 0.1800  198
4   CAA 0.3000  198
5   CAA 0.1100  198
6   CAA 0.2800  198

nb: You have not indicated how you will store the "factor" information.