Round Diff , Invoice Total be displaced

Question

Hi

I have below Data and i want if same DocNum repeats multiple times then RoundDif,InvoiceTotal should be displayed only once.

I want in Sql Query

Invoice No	Quantity	Basic Value	Tax Amount	Round Diff	Invoice Total
500273	513	18,940.00	2,272.80	-0.27	23,529.00
500273	309	1,963.11	353.36	-0.27	23,529.00
500286	965	3,840.55	460.87	-0.42	4,301.00

Thanks

Tuhin Paul · Answer

If you don’t need to retain all rows and only want one row per Invoice No , you can use a GROUP BY query: SELECT [Invoice No], SUM(Quantity) AS TotalQuantity, SUM([Basic Value]) AS TotalBasicValue, SUM([Tax Amount]) AS TotalTaxAmount, MAX([Round Diff]) AS [Round Diff], MAX([Invoice Total]) AS [Invoice Total] FROM YourTable GROUP BY [Invoice No]; This approach aggregates the data, displaying only one row per Invoice No .

Tuhin Paul · Answer

Input Data Invoice No Quantity Basic Value Tax Amount Round Diff Invoice Total 500273 513 18,940.00 2,272.80 -0.27 23,529.00 500273 309 1,963.11 353.36 -0.27 23,529.00 500286 965 3,840.55 460.87 -0.42 4,301.00 Output Invoice No Quantity Basic Value Tax Amount Round Diff Invoice Total 500273 513 18,940.00 2,272.80 -0.27 23,529.00 500273 309 1,963.11 353.36 NULL NULL 500286 965 3,840.55 460.87 -0.42 4,301.00

Tuhin Paul · Answer

WITH RankedData AS ( SELECT [Invoice No], Quantity, [Basic Value], [Tax Amount], [Round Diff], [Invoice Total], ROW_NUMBER() OVER (PARTITION BY [Invoice No] ORDER BY (SELECT NULL)) AS RowNum FROM YourTable ) SELECT [Invoice No], Quantity, [Basic Value], [Tax Amount], CASE WHEN RowNum = 1 THEN [Round Diff] ELSE NULL END AS [Round Diff], CASE WHEN RowNum = 1 THEN [Invoice Total] ELSE NULL END AS [Invoice Total] FROM RankedData ORDER BY [Invoice No], RowNum; ROW_NUMBER() Function : The ROW_NUMBER() function assigns a unique number to each row within a group of rows ( PARTITION BY [Invoice No] ). The ORDER BY (SELECT NULL) ensures that the rows are numbered arbitrarily within each group. You can replace (SELECT NULL) with a specific column (e.g., Quantity ) if you want to control the ordering. CASE Statement : The CASE statement checks if the RowNum is 1 (i.e., the first occurrence of the Invoice No ). If RowNum = 1 , it displays the Round Diff and Invoice Total . Otherwise, it sets these columns to NULL . WITH Clause (Common Table Expression) : The WITH RankedData clause creates a temporary result set that includes the RowNum column for each row. This makes it easier to filter or conditionally display data in the final SELECT statement. Final Output : The final SELECT statement retrieves all columns but conditionally displays Round Diff and Invoice Total only for the first row of each Invoice No .

Tuhin Paul · Answer

To achieve the desired output where Round Diff and Invoice Total are displayed only once for repeated DocNum values, you can use a ROW_NUMBER() function in SQL to identify the first occurrence of each DocNum . Then, you can conditionally display the Round Diff and Invoice Total only for the first row of each group.

Eliana Blake · Answer

To achieve the desired outcome where the Round Diff and Invoice Total are displayed only once for each DocNum, you can leverage SQL to aggregate the data using GROUP BY. Here's an example SQL query based on your provided table structure: SELECT [Invoice No], SUM(Quantity) AS TotalQuantity, SUM([Basic Value]) AS TotalBasicValue, SUM([Tax Amount]) AS TotalTaxAmount, MAX([Round Diff]) AS RoundDiff, -- Displaying the Round Diff only once MAX([Invoice Total]) AS InvoiceTotal -- Displaying the Invoice Total only once FROM YourTableName -- Replace YourTableName with the actual table name GROUP BY [Invoice No]; In this query: - We are grouping the data by [Invoice No]. - The SUM function is used to calculate the total Quantity, Basic Value, and Tax Amount for each unique [Invoice No]. - The MAX function is used to display the Round Diff and Invoice Total only once for each [Invoice No]. By running this SQL query on your dataset, you should be able to achieve the desired result by consolidating the Round Diff and Invoice Total values for each unique Invoice No. If you encounter any issues or need further clarification, feel free to ask!