Search code examples
sqlpostgresqldatabase-administration

How can I get 0 as value in Count(field) when Group by value is not present in any row for fk_user_id

I have provided all required sql queries here. Currently when I run below Group By query, it gives me O/P for each fk_user_id if at-least 1 Colour is present for that user.

I already gone through all available solutions on Stack Overflow but in each solution there is only 1 field as group by and I want to group by on 2 fields in sequence.

I want count(*) as 0 value for any user if Colour is not available for that user.

For ex. in below query, fk_user_id (37 and 75) contains only 1 row for Colour White and Black respectively. I want same result with 2 more rows as for

fk_user_id=37 and color Black as 0 vote and

fk_user_id=75 and color White as 0 vote

Create Table Query

CREATE TABLE public.color_value
(
  id bigint,
  color character varying(15),
  fk_user_id bigint
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.color_value
  OWNER TO postgres;

Insert Data Query

INSERT INTO public.color_value(id, color, fk_user_id)
    VALUES
(1, 'Black', 15), (2, 'White', 27), (3, 'White', 54), (4, 'Black', 75), (5, 'Black', 27), (6, 'Black', 97), (7, 'White', 37), 
(8, 'Black', 58), (9, 'White', 15), (10, 'Black', 54), (11, 'White', 97), (12, 'White', 58), (13, 'White', 54), (14, 'Black', 75),
(15, 'Black', 27), (16, 'Black', 97), (17, 'White', 37), (18, 'Black', 58), (19, 'Black', 15), (20, 'White', 27), (21, 'White', 54),
(22, 'Black', 75), (23, 'Black', 27), (24, 'Black', 97), (25, 'White', 37), (26, 'Black', 58), (27, 'White', 15), (28, 'White', 37),
(29, 'Black', 58), (30, 'White', 15), (31, 'Black', 54), (32, 'White', 97), (33, 'White', 58), (34, 'White', 54), (35, 'Black', 75);

Select Query With Group By

select count(*) AS vote, color, fk_user_id 
from color_value 
group by fk_user_id, color 
order by fk_user_id, color;

Current Output

enter image description here

Thank you in advance.

Solution

  • You can enumerate the users and colors first, then bring the table with a left join:

    select count(cv.color) AS vote, u.fk_user_id, c.color 
from (select distinct color from color_value) c
cross join (select distinct fk_user_id from color_value) u
left join color_value cv on cv.fk_user_id = u.fk_user_id and cv.color = c.color
group by u.fk_user_id, c.color 
order by u.fk_user_id, c.color;