How can I summarize a set of records into one line where only one record in a column has text and al-CodePudding

I have invoice data stored in a SQL database and I need a summary query to bring up the Invoice Number, PO number, Date, and Invoice Amount in a single line using an MS Access Query. Unfortunately, the customer PO number is only on one line of the invoice data and pops up on the query result like this.

Invoice Date	Invoice #	PO Number	Amount
8/11/22	12345	NULL	$23.00
8/11/22	12345	456	$00.00

I need the output to look like this instead:

Invoice Date	Invoice #	PO Number	Amount
8/11/22	12345	456	$23.00

My query looks like this:

SELECT  
    [Invoice Date],
    [Invoice #],
    [PO Number],
    FORMAT$(Sum([Amount])),'$#,##0.00') AS [Amount]
FROM [Invoice Details]
GROUP BY 
    [Invoice Date],
    [Invoice #],
    [PO Number]
HAVING 
    [INVOICE DATE] BETWEEN [8/11/2022] AND [8/11/2022]
ORDER BY 
    [INVOICE #]

I am still a novice when it comes to SQL queries and am not sure what I am missing here. Any help is appreciated.

CodePudding user response：

Then you can exclude [PO Number] column from your GROUP BY and just take the greater value in each group. I think you can use :

SELECT  
    [Invoice Date],
    [Invoice #],
    MAX(Nz([PO Number], 0)) AS [PO Number],
    FORMAT$(Sum([Amount])),'$#,##0.00') AS [Amount]
FROM [Invoice Details]
GROUP BY 
    [Invoice Date],
    [Invoice #],
HAVING 
    [INVOICE DATE] BETWEEN [8/11/2022] AND [8/11/2022]
ORDER BY 
    [INVOICE #]