user202944 user202944 - 1 month ago 7
SQL Question

add count for all rows ignoring group by in SQL

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

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;
Comments