The following Microsoft SQL Server T-SQL script illustrates the exclusion of some rows from the first total while including all rows in the second total:

-- SQL excluding some rows from GROUP BY aggregate functions

-- T-SQL CASE function usage example

USE AdventureWorks2008;

SELECT   Color = ISNULL(Color,'NOCOLOR'),

         PartialStandardCostTotal = SUM(CASE

                                   WHEN DaysToManufacture > 2 THEN StandardCost

                                   ELSE 0

                                 END),

         StandardCostTotal = SUM(StandardCost),

         ItemsWithExclusion = COUNT(CASE

                                   WHEN DaysToManufacture > 2 THEN 1

                                   ELSE NULL

                                 END),

         Items = COUNT(StandardCost)

FROM     Production.Product

GROUP BY Color

ORDER BY Color

GO

/*

Color       PartialSCT  StandardCostTotal ItemsWithExclusion      Items

Black       25729.5912  38636.5002        30                      93

Blue        11255.5788  14746.1464        13                      26

Grey        0.00        51.5625           0                       1

Multi       0.00        272.2542          0                       8

N/C         0.00        2238.4755         0                       248

Red         24311.5592  32610.7661        20                      38

Silver      14665.6792  20060.0483        16                      43

…..

*/