add count for all rows ignoring group by in 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

All you need is ROW_NUMBER. You just have to decide what column to use as an order. https://msdn.microsoft.com/en-us/library/ms186734.aspx The order by clause you have is pointless because it is ordering by the string literal, not the column alias. Which means you are going to get some order but you don't know what. You also might consider not splattering NOLOCK all over the place. It is far more sinister than most people realize. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Oct 20 '16 at 16:30
Thank you for the information on NO LOCK. That part wasn't something I did but the people who created the default base code for interaction with the database did. I may remove that and see what the outcome is! — user202944, Oct 20 '16 at 17:16

score 1 · Accepted Answer · answered Oct 20 '16 at 16:54

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;

Absolutely perfect, thank you. That was the exact thing i have been searching for for the best part of a day! Now to work out how to get the final total as the final amount rather than the first and i am set :D — user202944, Oct 20 '16 at 17:21

add count for all rows ignoring group by in SQL

1 Answers1