How to group by in plpgsql

Question

I'm having trouble in grouping my data where I am grabing records and grouping them by employer_type and date. Both have same date and same employer type but doesnt group instead returns 2 different obj. What could have been giving me this output? How to group them correctly?

CREATE OR REPLACE FUNCTION get_employer_profiles_count(provided_date date)
RETURNS TABLE (
  is_employer boolean,
  type text,
  date date,
  item_count integer
)
LANGUAGE sql security definer
AS $$
  SELECT
    pr.is_employer, case when pr.is_employer then 'company' else 'employer' end as type,  pr.created_at, count(pr.id) as count
  FROM profiles as pr
  -- WHERE usp.created_at BETWEEN date_trunc('day', provided_date) and date_trunc('day', provided_date) + interval '23:59:59'
  WHERE pr.created_at BETWEEN date_trunc('day', provided_date) and date_trunc('day', provided_date) + interval '23:59:59'
  GROUP BY pr.is_employer, pr.created_at, type
  ORDER BY pr.created_at;
$$;
-- select json_build_object('is_employer', is_employer, 'count', item_count, 'date', date, 'type', type) from get_employer_profiles_count('2023-07-24');

The output Im having is [{"is_employer":true,"count":1,"date":"2023-07-24","type":"company"},{"is_employer":true,"count":1,"date":"2023-07-24","type":"company"}]

JohnH · Answer 1 · 2023-07-27T09:20:33.303

Although the rows have the same date, they don't have the same value for created_at. Group by date_trunc('day', created_at) instead of created_at. There is also an issue with the check of created_at occurring on the provided date; e.g., when created_at has a time component after 23:59:59 but before midnight of the next day, it will not be returned by the query. Instead of using BETWEEN, test for created_at >= provided_date AND created_at < provided_date + interval '1' day. Time ranges should be handled as half-open ranges so that queries need not be aware of the type's resolution.

How to group by in plpgsql

1 Answers1