How we can use RowNumber()

Question

Hi

I have below query .

SELECT

A."DocNum",A."DocDate",A."CardCode",A."CardName",

A."StcCode", A."CGST",A."SGST",A."IGST",A."LocGSTN",A."BpGSTN",A."Total Amount"

from (

select

t0."DocNum",t0."DocDate",Max(t0."CardCode") As "CardCode",Max(t0."CardName") As "CardName",t2."StcCode",


(Select SUM(A0."TaxSum") from Inv4 A0 where A0."DocEntry" = T0."DocEntry" and A0."staType" = -100 and A0."StcCode" = t2."StcCode") as "CGST",

Max(T0."DocTotal"+T0."DpmAmnt") As "Total Amount"

from oinv t0

left join inv4 t2 on t2."DocEntry" = t0."DocEntry"

where t0."DocDate" >= '2014/12/01' AND  t0."DocDate" <= '2025/03/01'

group by t0."DocEntry",t0."DocNum",t0."DocDate",t2."StcCode",t0."CardCode",t0."ShipToCode"

I want data like below . If 1 Doc No has multiple rows then Grand Total should appear only once. How we can use RowNumber() in above query

19137	CSGST-18	13525.2	28150
19137	CSGST-18	13525.2	28150
19137	CSGST-18	384.95	28150
19137	CSGST-18	384.95	28150
19137	CSGST-18	164.52	28150
19137	CSGST-18	164.52	28150

19137	CSGST-18	13525.2	28150
19137	CSGST-18	13525.2
19137	CSGST-18	384.95
19137	CSGST-18	384.95
19137	CSGST-18	164.52
19137	CSGST-18	164.52

Thanks

Jignesh Kumar · Answer

Hello Ramco, WITH CTE AS ( SELECT t0."DocNum", t0."DocDate", MAX(t0."CardCode") AS "CardCode", MAX(t0."CardName") AS "CardName", t2."StcCode", (SELECT SUM(A0."TaxSum") FROM Inv4 A0 WHERE A0."DocEntry" = T0."DocEntry" AND A0."staType" = -100 AND A0."StcCode" = t2."StcCode") AS "CGST", MAX(T0."DocTotal" + T0."DpmAmnt") AS "Total Amount", ROW_NUMBER() OVER (PARTITION BY t0."DocNum" ORDER BY t2."StcCode") AS "RowNum" FROM oinv t0 LEFT JOIN inv4 t2 ON t2."DocEntry" = t0."DocEntry" WHERE t0."DocDate" >= '2014/12/01' AND t0."DocDate"

Sangeetha S · Answer

SELECT RowNum, DocNum, DocDate, CardCode, CardName, StcCode, CGST, SGST, IGST, LocGSTN, BpGSTN, "Total Amount" FROM ( SELECT ROW_NUMBER() OVER (ORDER BY T0."DocDate", T0."DocNum") AS RowNum, T0."DocNum", T0."DocDate", MAX(T0."CardCode") AS CardCode, MAX(T0."CardName") AS CardName, T2."StcCode", (SELECT SUM(A0."TaxSum") FROM Inv4 A0 WHERE A0."DocEntry" = T0."DocEntry" AND A0."staType" = -100 AND A0."StcCode" = T2."StcCode") AS CGST, MAX(T0."DocTotal" + T0."DpmAmnt") AS "Total Amount", -- Add other needed columns here as well. T0."LocGSTN", T0."BpGSTN", T0."SGST", T0."IGST" FROM OINV T0 LEFT JOIN Inv4 T2 ON T2."DocEntry" = T0."DocEntry" WHERE T0."DocDate" >= '2014/12/01' AND T0."DocDate"

Emily Foster · Answer

Hi! It seems like you're aiming to include row numbers in your SQL query output to achieve a specific layout where the grand total appears only once for each document number that has multiple rows. The `RowNumber()` function in SQL can be a handy tool for achieving this. Here’s a simplified explanation on how you can use `RowNumber()` in your scenario: 1. Use of `RowNumber()` Function : - `RowNumber()` is a window function in SQL that assigns a unique sequential integer to each row within a partition of a result set. It is commonly used for ranking and pagination purposes. 2. Applying `RowNumber()` in Your Query : - To implement `RowNumber()` in your existing query, you would typically add it as a column in your select statement. This column would provide a sequential row number for each row, which can then be used to identify when to display the grand total. 3. Partitioning and Ordering : - You can specify the partitioning of rows based on the document number to ensure the row numbers restart for each new document number. This way, the grand total can be displayed only once per document number. 4. Display Logic : - With the row numbers in place, you can conditionally show the grand total row based on the row number. For instance, displaying the grand total only when the row number is the last row for a specific document number. Here’s a simplified example snippet showcasing the inclusion of `RowNumber()` in your query. This example assumes a simplified structure and may need adjustments based on your database schema and query complexity: SELECT *, ROW_NUMBER() OVER(PARTITION BY DocNum ORDER BY DocDate) AS RowNum FROM YourTableName WHERE -- Add your date conditions here ORDER BY DocNum, RowNum; By incorporating `RowNumber()` in your query and utilizing proper logic based on the row numbers generated, you can achieve the desired layout where the grand total appears only once for each document number with multiple rows. Feel free to adapt this guidance to your specific SQL query and database structure. If you need further assistance or clarity on any specific aspect, feel free to ask. Good luck with implementing `RowNumber()` in your query!