Part 2: how to get the Sum of a partition based query without actually pivoting

Question

so since I dont have the latest version of Postgresql (I have Postgresql 9.3) its been impossible for me to come up with a function similar to the pivot function that calculates the total value of a particalar field.

You can click the link below to use a question I created as reference, they are similar in code but different in the aspect of requirements but there is a second query that uses array and also produces the exact same results.

How to create columns for different fields without applying the pivoting function

I currently get the following results

I want these results

Below I have a query that returns me the languages the students speaks in 3 different columns... My problem is that I cant come up with a solution that can possibly return me the sum of how many students speak 1 language1, how many speak language2 , how many speak language3

with t as (
  SELECT s.studentnumber as studentnr, p.firstname AS name, 
sl.gradenumber as gradenumber, l.text as language,
  dense_rank() over (partition by s.studentnumber, 
p.firstname, sl.gradenumber order by l.text) as seqnum

  FROM student s JOIN 
       pupil p
       ON p.id = s.pupilid JOIN
       pupillanguage pl 
       ON pl.pupilid = p.id JOIN
       language l 
       ON l.id = pl.languageid JOIN
       schoollevel sl
       ON sl.id = p.schoollevelid
 )
select studentnr, name, gradenumber,
   max(case when seqnum = 1 then language end) as language_1,
   max(case when seqnum = 2 then language end) as language_2,
   max(case when seqnum = 3 then language end) as language_3
from t
group by studentnr, name, gradenumber;

Im asking this question because if there is no way to do this then there is no need for me to look into it further if its not possible.

This entire concept of partition and denserank is relatively new to me and I'm not sure about their extent and capabilities of producing further results.

Answer 1

Use the the solution you have (either one, I prefer the array solution for obvious reasons), put it into a CTE, then use UNION to calculate the totals:

with students as (
  select studentnr, 
         name, 
         gradenumber, 
         languages[1] as language_1,
         languages[2] as language_2,
         languages[3] as language_3,
         languages[4] as language_4,
         languages[5] as language_5
  FROM (       
    SELECT s.studentnumber as studentnr, 
           p.firstname AS name,
           sl.gradenumber as gradenumber,
           array_agg(DISTINCT l.text) as languages
    FROM student s
        JOIN pupil p ON p.id = s.pupilid    
        JOIN pupillanguage pl on pl.pupilid = p.id
        JOIN language l on l.id = pl.languageid
        JOIN schoollevel sl ON sl.id = p.schoollevelid
    GROUP BY s.studentnumber, p.firstname
  ) t
)
select *
from students
union all
select null as studentnr,
       null as name, 
       null as gradenumber, 
       count(language_1)::text,
       count(language_2)::text, 
       count(language_3)::text, 
       count(language_4)::text, 
       count(language_5)::text
from students;

Aggregate functions like count() ignore NULL values, so it will only count rows where a language exists.

The data types of all columns in the queries of a UNION have to match, so you can't return integer values in a column in the second query if the first query defines that column as text (or varchar). That's why the result of the count() needs to be cast to text

The column aliases in the second query aren't really necessary, but I have added them to show how the column lists have to match