CODEWITHSUNDEEP
sqlsql-servercount
Benzz
Benzz

Reputation: 119

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

Upvotes: 0

Views: 479

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

Related Questions