How to Group by and Return Sum Row in Postgres

how to group by and return sum row in Postgres

SELECT id, SUM(amount) AS amount
FROM yourtable
GROUP BY id

Postgres, add the sum of values of GROUP BY records

Since you don't provided any info about how retrieve latitude and longitude I'm assuming last_value and max, respectively for each query:

--Sample
CREATE TEMP TABLE mission 
    (id, job_id, date) AS
    VALUES 
        (1893, 'HAM267'::TEXT, now()),
        (1894, 'XYZ', now());

CREATE TEMP TABLE media 
    (id, mission_id, location_latitude, location_longitude, status) AS
    VALUES 
        (1, 1893, 51.501671242483, -0.22951886283813, 2),
        (2, 1893, 51.501577520364, -0.22880621040992, 1),
        (3, 1893, 51.50137231105, -0.22878203293347, 0),
        (4, 1893, 51.501506139391, -0.22880008494156, 2),
        (5, 1894, 51.501671242466, -0.22951886283812, 7);

--Using DISTINCT ON. 
--You should add ORDER BY clause in OVER to avoid unpredictable results
SELECT
    DISTINCT ON (mis.id, mis.job_id, mis.date)
    mis.id AS "ID",
    mis.job_id AS "Job ID",
    to_char(mis.date, 'DD/MM/YYYY') AS "Date",
    first_value(med.location_latitude) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Lat", 
    first_value(med.location_longitude) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Long",
    sum(med.status) 
        OVER(PARTITION BY mis.id, mis.job_id, mis.date) AS "Status Aggregate"                                       
FROM mission mis
  LEFT JOIN media med
    ON mis.id = med.mission_id
ORDER BY
    mis.id, mis.job_id, mis.date;

--Using GROUP 
SELECT
    mis.id AS "ID",
    mis.job_id AS "Job ID",
    to_char(mis.date, 'DD/MM/YYYY') AS "Date",
    max(med.location_latitude) AS "Lat",
    max(med.location_longitude) AS "Long",
    sum(med.status) AS "Status Aggregate"                                       
FROM mission mis
  LEFT JOIN media med
    ON mis.id = med.mission_id
GROUP BY
    mis.id, mis.job_id, mis.date
ORDER BY
    mis.id, mis.job_id, mis.date;

PostgreSQL group by with sum

you need to use the aggregate function SUM() and grouped them by m_warehouse_id

SELECT  m_warehouse_id, SUM(qtyonhand) totalQuantity
FROM adempiere.rv_storage
WHERE rv_storage.m_product_id=1000412
GROUP BY m_warehouse_id
ORDER BY m_warehouse_id;

PostgreSQL Group By Sum

Using stored functions allows to avoid (sometime) the head-breaking queries.

create or replace function fn_foo(ids out int[], characters out int) returns setof record language plpgsql as $$
declare
  r record;
  threshold int := 100;
begin
  ids := '{}'; characters := 0;
  for r in (
    select id, coalesce(length(content),0) as lng
    from test order by id)
  loop
    characters := characters + r.lng;
    ids := ids || r.id;
    if characters > threshold then
      return next;
      ids := '{}'; characters := 0;
    end if;
  end loop;
  if ids <> '{}' then
    return next;
  end if;
end $$;

select * from fn_foo();

╔═══════╤════════════╗
║  ids  │ characters ║
╠═══════╪════════════╣
║ {1,2} │        129 ║
║ {3,4} │        113 ║
║ {5}   │        120 ║
║ {6,7} │        172 ║
║ {8}   │         35 ║
╚═══════╧════════════╝
(5 rows)

SQL: Group by Case Statement to sum Row Values

You want a GROUP BY based on an expression. You also need to put the wildcard for the LIKE condition after the prefix you want, 'cf%'

SELECT CASE 
          WHEN subject LIKE 'cf%' THEN 'cf' 
          else subject
       end as normalized_subject,
       sum("count")
FROM the_table
group by normalized_subject
order by sum("count") desc

Using a column alias in the GROUP BY is a Postgres extension to the SQL language. In other DBMS products you would need to repeat the CASE expression in the GROUP BY (which is only more to type, but won't change performance)

Online example

How to Sum values by day and group in postgresql?

Using the aggregate function sum and grouping by date and group will achieve this. Since you have timestamp data, the solution below casts it to a date type and groups using that. Finally in the projection, I also casted to a text to remove the additional date information and just provide with YYYY-MM-DD

Schema (PostgreSQL v11)

CREATE TABLE my_table (
  "date" TIMESTAMP,
  "value" INTEGER,
  "Group" VARCHAR(1)
);

INSERT INTO my_table
  ("date", "value", "Group")
VALUES
  ('2021-04-07 00:00:00', '5', 'a'),
  ('2021-04-07 00:00:00', '10', 'b'),
  ('2021-04-07 01:00:00', '5', 'a'),
  ('2021-04-07 01:00:00', '4', 'b'),
  ('2021-04-08 00:00:00', '5', 'a'),
  ('2021-04-08 00:00:00', '8', 'b'),
  ('2021-04-08 01:00:00', '4', 'a'),
  ('2021-04-08 01:00:00', '5', 'b');

---

Query #1

select
   "date"::date::text,
   sum(value) as total_value,
   "Group"
FROM
   my_table
GROUP BY
   "date"::date, "Group";

---