The super semicolon of SQL

One of my assignments was to do a query that returned three values. How many people gave gifts that were small,  medium, or large.  I found the answer was to use a semicolon. Here are two cool solutions. The first is specific for transact SQL or T-SQL and the next one is more generic using semicolons as promised.


USE CRM: GO DECLARE @SmallDown NVARCHAR(0);

DECLARE @SmallUp NVARCHAR(100);

DECLARE @MediumDown NVARCHAR(101);

DECLARE @MediumUp NVARCHAR(1000);

DELARE @LargeDown NVARCHAR(1001);

COUNT Gift (IF BETWEEN SmallDown AND SmallUp) FROM Contribution
AS SmallGiftsCount;
SET @SGC = SmallGiftsCount;
AVG Gift (IF BETWEEN SmallDown AND SmallUp) FROM Contribution AS SmallGiftsAVG;
SET @SGA = SmallGiftsAVG;
SUM Gift (IF BETWEEN SmallDown AND SmallUp) FROM Contribution AS SmallGiftsSum;
FROM Contribution AS SmallGiftSUM;
SET @ SGS = SmallGiftSUM;

COUNT Gift (IF BETWEEN MediumDown AND MediumUp) FROM Contribution

AS MediumGiftCount;
SET @MGC = MediumGiftCount;
AVG Gift (IF BETWEEN MediumDown AND MediumUp) FROM Contribution AS MediumGiftAVG;
SET @MGA = MediumGiftAVG;
SUM Gift (IF BETWEEN MediumDown AND MediumDown) FROM Contribution AS MediumGiftSUM;

SET @MGS = MediumGiftSUM;

COUNT Gift (IF LargeDown =<) FROM Contribution AS LargeGiiftsCount;
SET @LGC = LargeGiftsCount;

AVG Gift (IF LargeDown =<) FROM Contribution AS MediumGiftAVG;
SET @LGA = LargeGiftAVG;
SUM Gift (IF BETWEEN MediumDown AND MediumDown) FROM Contribution AS MediumGiftSUM;

SET @LGS = LargeGiftSUM;

PRINT 'Report'

PRINT 'About' + @ SGC + 'people contributed' + @SGS ' averaging  gifts of' + SGA + '.'

PRINT 'Report'

PRINT 'About' + @ MGC + 'people contributed' + @MGS ' averaging  gifts of' + MGA + '.'

PRINT 'Report'

PRINT 'About' + @ LGC + 'people contributed' + @LGS ' averaging  gifts of' + LGA + '.'

Note:

Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.

If the donations are made in chunks here is the way combine them all  HAVING SUM(SaleAmount) > 1000

Two neat tools that are available is called linqpad, https://www.linqpad.net and EMS lite 
https://www.sqlmanager.net/en/tools/free

I am working on three posts,  one for data entity framework another one for desired state configuration with Chef,  one about Asterisk PBS from Digium.