I have a table:

CREATE TABLE schools (
    ID int,
    type varchar(255)
);

INSERT INTO schools (ID, type)
VALUES (1, NULL),
(2, 'primary'),
(3, 'secondary'),
(4, 's'),
(5, 'p'),
(5, 'p');

I need to produce a table like this:

Type      Volume %
Primary   2     50
Secondary 2     50

So far I have the first two columns by using the query:

SELECT CASE 
  WHEN type IN ('primary','p') THEN 'Primary'
  WHEN type IN ('secondary','s') THEN 'Secondary'
END Type,
count(distinct ID) as Volume
FROM t

I cant figure out how to get the Volume as a percentage. Using partition by and over keeps throwing aggregate errors. Could someone please explain how I can do this?

Also, I want the percentage to not include NULL, hence why it has 50%.

CodePudding user response：

Use SUM() window function to get the total:

SELECT CASE 
         WHEN type IN ('primary','p') THEN 'Primary'
         WHEN type IN ('secondary','s') THEN 'Secondary'
       END type,
       COUNT(*) AS Volume,
       100 * COUNT(*) / SUM(COUNT(*)) OVER () AS percentage
FROM (SELECT DISTINCT ID, type FROM schools) s
WHERE type IS NOT NULL
GROUP BY 1;

See the demo.

CodePudding user response：

You can use following query

SELECT Type,
       Volume,
       CAST(Volume AS Float) / SUM(Volume) OVER(PARTITION BY seq ORDER BY seq) * 100 AS '%'
FROM
   (SELECT Type,
        COUNT(DISTINCT ID) AS Volume,
        RANK() OVER(ORDER BY (SELECT NULL)) as seq
   FROM
      (SELECT ID,
        CASE 
         WHEN type IN ('primary','p') THEN 'Primary'
         WHEN type IN ('secondary','s') THEN 'Secondary'
        END Type
       FROM t) t
   WHERE Type IS NOT NULL
   GROUP BY Type) t

Demo in db<>fiddle