Postgres, add the sum of values of GROUP BY records

Question

Trying to write a query that will get the sum of the joined table's column status

The two tables I am joining are mission and media. A mission record can have 0-->n media records.

Currently my querie returns a result like this.

e.g.

id      job_id  date        lat             long                status
1893    HAM267  13:53:42    51.501671242483 -0.22951886283813   2
1893    HAM267  13:53:42    51.501577520364 -0.22880621040992   1
1893    HAM267  13:53:42    51.50137231105  -0.22878203293347   0
1893    HAM267  13:53:42    51.501506139391 -0.22880008494156   2

However I want to group all related media records and add their status column value.

1893    HAM267  13:53:42    51.501671242483 -0.22951886283813   5

I tried doing a sum on a subquery and then grouping by each column, however this did not have the desired effect with an error:

[21000] ERROR: more than one row returned by a subquery used as an expression

Query

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

Answer 1

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;