Question 252120 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Group by with count along with total count in all the rows

I am performing group by to get the count of every individual value along with the total count.

Data,

enter image description here

It is very easy to get the count of individual columns using below queries,

select name, colour, count(1) from table group by name, colour;
select colour, count(1) from table group by colour;

I need both the results in a single query. Expected result is,

enter image description here

I need total count of Name in every row with respect to Colour.
Any suggestion would be appreciated

Tags:

2

Answers


  1. 0 
    -- Create the table
DROP TABLE if exists your_table;
CREATE TABLE your_table (
    name VARCHAR(255),
    colour VARCHAR(255)
);

-- Insert sample data
INSERT INTO your_table (name, colour)
VALUES
    ('John', 'Red'),
    ('Sara', 'Blue'),
    ('John', 'Red'),
    ('Mike', 'Green'),
    ('Sara', 'Blue'),
    ('John', 'Green');

-- Query 1: Calculate counts using window functions
SELECT DISTINCT
    name,
    colour,
    COUNT(1) OVER (PARTITION BY name, colour) AS count_by_name_colour,
    COUNT(1) OVER (PARTITION BY name) AS count_by_name
FROM mytable;

-- Query2 to get individual and total counts
WITH IndividualCounts AS (
  SELECT name, colour, COUNT(*) AS individual_count
  FROM your_table
  GROUP BY name, colour
),
TotalCounts AS (
  SELECT colour, COUNT(*) AS total_count
  FROM your_table
  GROUP BY colour
)
SELECT
  ic.name,
  ic.colour,
  ic.individual_count,
  tc.total_count
FROM
  IndividualCounts ic
JOIN
  TotalCounts tc
ON
  ic.colour = tc.colour;
    Login or Signup to reply.
  2. 0

    You can use window function count() :

    select distinct name, colour, 
       count(1) over (partition by Name, colour) as count_by_name_colour, 
       count(1) over (partition by name) as count_by_name
from mytable

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search