Skip to main content

Posts

Showing posts from January, 2006

SQL Quirks - count and sum

I was reminded of a little quirk of SQL recently, and I thought I would share it with my readers. I will be going over the SQL in a manner suitable for beginners, but the quirk that I refer to could catch an intermediate student of SQL. I will use some simple SQL to illustrate my point. -- 1. create temporary table CREATE TABLE #test1 ( id int not null , name varchar(50) not null ) ; -- 2. declare variables DECLARE @cnt int, @sum int ; -- 3. compute statistics SELECT @cnt = count(*) FROM #test1 ; SELECT @sum = sum(id) FROM #test1 ; -- 4. print results to query analyzer PRINT '@cnt = ' + ISNULL( CONVERT(varchar, @cnt), '[NULL]') ; PRINT '@sum = ' + ISNULL( CONVERT(varchar, @sum), '[NULL]') ; -- 5. cleanup DROP TABLE #test1 ; If you will paste this sequence into query analyzer, and run it in a non-production database, you will see that it produces: @cnt = 0 @sum = [NULL] In step 1, we create an empty table. In step 2, we declare two variables, and their