Friday, October 01, 2010

Aggregrate Queries in SQL Server T-SQL for Beginners By Kathi Kellenberger

My learnings from the sql share video

  • I knew that we cannot use where clause with aggregate functions but I didn't know the reason. The reason is the where clause is executed before the aggregate functions are applied and hence cannot use the where clause with.
  • Count(*) and count(columnname) may yield different results. This is because Count(*) does not ignore null values and count(columnname) ignores or does not count null values.
    When you do aggregation using the AVG function using the isnull function is recommended. Use of isnull function to substitute the null value of the column will give you the correct answer. eg: AVg(IsNull(columnname,0)
  • Always remember to supply an alias for each aggregated function
  • You can use other expression in the aggregate functions instead of just the column names. eg: sum(1) as "sum of ones"
  • When you use a group by clause, make sure select items are exactly same as group by clause, otherwise you might get incorrect results.

No comments:

INSERT EXEC failed because the stored procedure altered the schema of the target table.

This morning I was trying to call a stored procedure and insert the results in a temp table using the following code  -- create table #...