Multi tool use

How to Count per line item

I have a set of results, example would be an invoice.

They want a set of results which list the items per invoice but with a line number next to each one.

What I currently have (amended to suit question);

SELECT
[jvi].[name] AS [ScheduledCode],
[jvi].[description] AS [Description],
[clientReference] AS [TracksheetRef],
(SELECT TOP 1 COUNT(*) FROM [table2items] [jvi1] WHERE [jvi1].[SheetId] = [jvs].[id]) AS [Line],
(SELECT TOP 1 SUM(CAST(([jvi1].[Cost] * [jvi1].[qty]) AS MONEY)) FROM [table2items] [jvi1] WHERE [jvi1].[valuationSheetId] = [jvs].[id]) AS [UpliftedValue],

FROM
[dbo].[table1invoice] [jvs]
INNER JOIN
[dbo].[table2items] [jvi] ON [jvi].[SheetId] = [jvs].[id]



ScheduledCode Description Ref Lines UpliftedValue
C142011 RepairMain/90-150mm/Unmade 0227-0318 1 303.68
C182912 Surfaced 4237-0518 1 211.58
C182912 Install 4626-0518 2 356.24
C182811 Investigation / Unmade 4626-0518 2 356.24
C182811 Poor Supply 3460-0118 2 356.24


As you can see, it counts how many lines there are per sheet, but I need it to add a number next to each sheet item and count upward, example:

1 C182811 Investigation / Unmade 4626-0118 2 356.24
2 C182811 Poor Supply 3460-0118 2 356.24
1 C182912 Surfaced 4237-0518 1 211.58
2 C182912 Install 4626-0518 2 356.24


1 Answer
1

Just Use :

row_number() over
(partition by ScheduledCode order by ScheduledCode, UpliftedValue, Description) as rowNum


as the leftmost column for your query as in the following :

SELECT row_number() over
(partition by q.ScheduledCode order by q.ScheduledCode, q.UpliftedValue,q.Description ) as rowNum,
q.*
FROM
(
SELECT
[jvi].[name] AS [ScheduledCode],
[jvi].[description] AS [Description],
[clientReference] AS [TracksheetRef],
(SELECT TOP 1 COUNT(*) FROM [table2items] [jvi1] WHERE [jvi1].[SheetId] = [jvs].[id]) AS [Line],
(SELECT TOP 1 SUM(CAST(([jvi1].[Cost] * [jvi1].[qty]) AS MONEY)) FROM [table2items] [jvi1] WHERE [jvi1].[valuationSheetId] = [jvs].[id]) AS [UpliftedValue],

FROM
[dbo].[table1invoice] [jvs]
INNER JOIN
[dbo].[table2items] [jvi] ON [jvi].[SheetId] = [jvs].[id]
) q


SQL Fiddle Demo

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.