2

I am new to Postgres and databases so I am sorry. I ran a query to get a count of students per school from one table. Now I have the table below: 

school_probs:

school_code(PK bigint)  schoolName(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}

How would I go about multiplying the count from each school with each number in the probs column. Ex: We have 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). With the results I can then assign grades to students.

My query to get the count is as follows:

SELECT simulated_records.school, COUNT(simulated_records.school) as studentCount INTO CountSchool
FROM simulated_records, school_probs
WHERE simulated_records.school = school_probs.school
GROUP BY simulated_records.school;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
CCat
  • 123
  • 10

1 Answers1

3

To multiply elements of an array with a constant, you need to unnest, multiply, and aggregate again. Some caveats lie in wait. Consider:

And best use an ARRAY constructor.

That said, and making some assumptions about your undisclosed table design, I would also simplify the count:

Arriving at:

SELECT *, ARRAY(SELECT unnest(p.probs) * r.student_ct) AS scaled_probs
FROM   school_probs p
LEFT   JOIN  (
   SELECT school, COUNT(*)::int AS student_ct
   FROM   simulated_records
   GROUP  BY 1
   ) r USING (school);

Or, to represent NULL arrays as NULL arrays:

SELECT *
FROM   school_probs p
LEFT   JOIN  (
   SELECT school, COUNT(*)::int AS student_ct
   FROM   simulated_records
   GROUP  BY 1
   ) r USING (school)
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT unnest(p.probs) * r.student_ct) AS scaled_probs
   ) p1 ON p.probs IS NOT NULL;

db<>fiddle here

I suggest this simply form with a set-returning function in the SELECT list only for Postgres 10 or later, because of:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Perfect thank you! What I was looking for. How you would I round the results to be whole figures? – CCat Nov 05 '18 at 04:26
  • A simple cast (after the multiplication!) should do the job. `(unnest(probs) * 198)::int)`. See https://dbfiddle.uk/?rdbms=postgres_11&fiddle=dbbd9821f590b0a8902b69c7fdf7ced5 – Erwin Brandstetter Nov 05 '18 at 11:45