user202944 - 10 months ago 42

SQL Question

i have a query that i have been working on for a few days and thanks to this question SQL SERVER T-SQL Calculate SubTotal and Total by group i have been able to get my sub totals and my totals.

When this data is returned to my custom application (an application that i have no control over), it orders the returned data by the ID column ignoring my ORDER BY clause.

to fix this I want to add a dumb count to my TSQL so that a counter is incremented by 1 for every single row that is found regardless of what group the row was found in.

My code is as follows.

`select ID as ID, EstRefNum as "Estimate No",`

(case when GROUPING(Ordered) = 0 and

GROUPING(Ref1) = 0 and

GROUPING(ID) = 1

then

case when Ref1 IS NULL

then 'No BDM Sub Total'

else Ref1 + ' Sub Total'

end

when GROUPING(Ordered) = 0 and

GROUPING(Ref1) = 1 and

GROUPING(ID) = 1

then

CASE

WHEN Ordered = 0 THEN 'Not Ordered Sub Total'

WHEN Ordered = 1 THEN 'Ordered Sub Total'

WHEN Ordered = 2 THEN 'Superseded Sub Total'

WHEN Ordered = 4 THEN 'Won/Convert Sub Total'

WHEN Ordered = 5 THEN 'Lost Sub Total'

WHEN Ordered IS NULL THEN 'None Sub Total'

ELSE 'Unknown status code'

END

when GROUPING(Ordered) = 1 and

GROUPING(Ref1) = 1 and

GROUPING(ID) = 1

then

'Total'

else

case

when Ref1 IS NULL

then 'No BDM'

else Ref1

end

end) as "Sales Rep",

[mo].[EstimateDate] as "Estimate Date",

[mo].[CustomerRef] as "Customer",

[mo].[JobDescription] as "Title",

cast(Format([mo].[OtherTotal], 'N', 'en-us') as varchar(30)) as "Estimate total",

[mo].[TotalTotal] as "Sales",

[mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot] as "Direct Costs",

[mo].[TotalTotal] - ([mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot]) as "Value Added",

([mo].[OriginLabLabSubTot] + [mo].[PrintingSubTotal] + [mo].[FinishingSubTotal]) as "Overheads",

([mo].[TotalTotal] - ([mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot])) - ([mo].[OriginLabLabSubTot] + [mo].[PrintingSubTotal] + [mo].[FinishingSubTotal]) as "Profit",

sum(TotalTotal) as SubTotal,

CASE

WHEN [mo].[OtherTotal] = 0.000000 THEN '0.00'

WHEN [mo].[OtherTotal] = .00 THEN '0.00'

WHEN [mo].[OtherTotal] = 0.00 THEN '0.00'

ELSE LTRIM(Str(CAST([mo].[OtherTotal] as decimal(18,2)), 25, 2))

END AS 'CASE EST TOTAL',

CASE

WHEN Ordered = 0 THEN 'Not Ordered'

WHEN Ordered = 1 THEN 'Ordered'

WHEN Ordered = 2 THEN 'Superseded'

WHEN Ordered = 4 THEN 'Won/Convert'

WHEN Ordered = 5 THEN 'Lost'

WHEN Ordered IS NULL THEN 'None'

ELSE 'Unknown status code'

END AS 'Estimate Status'

From [dbo].[MainEstimateDetails] [mo] WITH (NOLOCK)

WHERE

[mo].[Ref1] LIKE 'STAFF MEMBER NAME'

group by grouping sets((Ordered, Ref1, ID, EstRefNum,EstimateDate, CustomerRef, JobDescription, OtherTotal, TotalTotal, PaperSubTot, OriginMatSubTot, OtherMatSubTotal, OutworkSubtot, OriginLabLabSubTot, PrintingSubTotal, FinishingSubTotal),

(Ordered, Ref1),

()

)

order by 'Estimate Status' Desc;

it returns data as follows.

What i get

All i want is a count on the left hand side as follows

What i want

How do i get the returned rows count ignoring the group by command? I have successfully managed to get the count of items within each group which is close but not what i am looking for. I just cannot work out how to manipulate a variable so that it is incremented by one on each loop through the database made by the select statement.

I have found questions and answers CLOSE to what i need but nothing that seems to be the silver bullet.

Thanks

Dean

Answer Source

As the comment said, you will just need to add a ROW_NUMBER column like below. It will increment an integer for each row.

```
select ID as ID, EstRefNum as "Estimate No",
(case when GROUPING(Ordered) = 0 and
GROUPING(Ref1) = 0 and
GROUPING(ID) = 1
then
case when Ref1 IS NULL
then 'No BDM Sub Total'
else Ref1 + ' Sub Total'
end
when GROUPING(Ordered) = 0 and
GROUPING(Ref1) = 1 and
GROUPING(ID) = 1
then
CASE
WHEN Ordered = 0 THEN 'Not Ordered Sub Total'
WHEN Ordered = 1 THEN 'Ordered Sub Total'
WHEN Ordered = 2 THEN 'Superseded Sub Total'
WHEN Ordered = 4 THEN 'Won/Convert Sub Total'
WHEN Ordered = 5 THEN 'Lost Sub Total'
WHEN Ordered IS NULL THEN 'None Sub Total'
ELSE 'Unknown status code'
END
when GROUPING(Ordered) = 1 and
GROUPING(Ref1) = 1 and
GROUPING(ID) = 1
then
'Total'
else
case
when Ref1 IS NULL
then 'No BDM'
else Ref1
end
end) as "Sales Rep",
[mo].[EstimateDate] as "Estimate Date",
[mo].[CustomerRef] as "Customer",
[mo].[JobDescription] as "Title",
cast(Format([mo].[OtherTotal], 'N', 'en-us') as varchar(30)) as "Estimate total",
[mo].[TotalTotal] as "Sales",
[mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot] as "Direct Costs",
[mo].[TotalTotal] - ([mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot]) as "Value Added",
([mo].[OriginLabLabSubTot] + [mo].[PrintingSubTotal] + [mo].[FinishingSubTotal]) as "Overheads",
([mo].[TotalTotal] - ([mo].[PaperSubTot] + [mo].[OriginMatSubTot] + [mo].[OtherMatSubTotal] + [mo].[OutworkSubtot])) - ([mo].[OriginLabLabSubTot] + [mo].[PrintingSubTotal] + [mo].[FinishingSubTotal]) as "Profit",
sum(TotalTotal) as SubTotal,
CASE
WHEN [mo].[OtherTotal] = 0.000000 THEN '0.00'
WHEN [mo].[OtherTotal] = .00 THEN '0.00'
WHEN [mo].[OtherTotal] = 0.00 THEN '0.00'
ELSE LTRIM(Str(CAST([mo].[OtherTotal] as decimal(18,2)), 25, 2))
END AS 'CASE EST TOTAL',
CASE
WHEN Ordered = 0 THEN 'Not Ordered'
WHEN Ordered = 1 THEN 'Ordered'
WHEN Ordered = 2 THEN 'Superseded'
WHEN Ordered = 4 THEN 'Won/Convert'
WHEN Ordered = 5 THEN 'Lost'
WHEN Ordered IS NULL THEN 'None'
ELSE 'Unknown status code'
END AS 'Estimate Status',
ROW_NUMBER() OVER(ORDER BY CASE
WHEN Ordered = 0 THEN 'Not Ordered'
WHEN Ordered = 1 THEN 'Ordered'
WHEN Ordered = 2 THEN 'Superseded'
WHEN Ordered = 4 THEN 'Won/Convert'
WHEN Ordered = 5 THEN 'Lost'
WHEN Ordered IS NULL THEN 'None'
ELSE 'Unknown status code'
END) AS RowID
From [dbo].[MainEstimateDetails] [mo] WITH (NOLOCK)
WHERE
[mo].[Ref1] LIKE 'STAFF MEMBER NAME'
group by grouping sets((Ordered, Ref1, ID, EstRefNum,EstimateDate, CustomerRef, JobDescription, OtherTotal, TotalTotal, PaperSubTot, OriginMatSubTot, OtherMatSubTotal, OutworkSubtot, OriginLabLabSubTot, PrintingSubTotal, FinishingSubTotal),
(Ordered, Ref1),
()
)
order by 'Estimate Status' Desc;
```