sqlstored-proceduresgrouping-sets

SQL Stored procedure - Grouping Sets with a where clause possible?

I have used Grouping Sets before to sum up everything in a column to get a grand total.

But, I have not found a way to sum up the columns with a WHERE CLAUSE included and I was wondering if there is actually a way to do this using Grouping Sets?

UPDATE

Here is an example of a basic query: I want to be able to sum up everything where Deleted = 0. Because I do not want the deleted transactions to be included in the Grand Total. But I still want the deleted transactions to show up in the result.

SELECT 
  TP.CompanyID, 
  TP.CompanyName, 
  TP.PaidStatus,'
  TP.Credit,
  TP.Debit,
  TP.Outstanding,
  TP.Deleted                           

FROM    
  dbo.Posting TP

GROUP BY
GROUPING SETS(( 
   CompanyID,
   CompanyName,
   PaidStatus,
   Credit,
   Debit,
   OutStanding,
   Deleted),())
Solution

  • You can use Where clause with GROUPING SETS like below:

    It should work.

    SELECT 
  TP.CompanyID, 
  TP.CompanyName, 
  TP.PaidStatus,'
  TP.Credit,
  TP.Debit,
  TP.Outstanding,
  TP.Deleted                           

FROM    
  dbo.Posting TP
WHERE TP.Deleted = 0
GROUP BY
GROUPING SETS(( 
   CompanyID,
   CompanyName,
   PaidStatus,
   Credit,
   Debit,
   OutStanding,
   Deleted),())