DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to exclude some rows from a sum?

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

…..

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE