I want to write a script that looks at a unique PO and determines whether that PO is closed or not based on the status of the line. Additionally, I want to sum the values of all rows for each PO. The below example shows what I am after.

• PO 41000934 is not closed because one of the lines (2) is still open
• PO 41000934 has remaining amount of 15,000
• PO 41000950 is closed because all lines are closed
• PO 41000950 has remaining amount of 14,000

CodePudding user response：

Please look into GROUP BY. https://www.w3schools.com/sql/sql_groupby.asp

To calculate sums, you can use the SUM function... And seeing as 'No' is smaller than 'Yes' (alphabetically) you can use the MIN function, which will return No if there are one or more rows with Closed = 'No' per PO.

SELECT 
  PO, 
  MIN(CLOSED) AS CLOSED, 
  SUM(REM_AMOUNT) AS REMAINING_AMOUNT
FROM TABLE
GROUP BY PO

CodePudding user response：

We can use the FUNCTION MIN() on closed to find out whether there are any lines which have the value of 'No'. We use the function SUM to add up the remaining amounts and we GROUP BY the value of PO.

create table poLines(
PO int,
Line int,
Closed char(3),
Rem_Amount int,
constraint ck_closed check (Closed in ('Yes','No') )
);
insert into poLines values
(41000934,    1,  'Yes',  10000),
(41000934,    2,  'No',   5000),
(41000950,    1,  'Yes',  4000),
(41000950,    2,  'Yes',  8000),
(41000950,    3,  'Yes',  2000);

SELECT
  PO,
  CASE WHEN MIN(Closed) = 'No' THEN 'open' 
       ELSE 'closed' END status,
  SUM(Rem_Amount) Remaining
FROM poLines
GROUP BY PO;

      PO | status | Remaining
-------: | :----- | --------:
41000934 | open   |     15000
41000950 | closed |     14000

db<>fiddle here