Rob Farley

Rob Rob Farley has been consulting in IT since completing a Computer Science degree with first class honours in 1997. Before moving to Adelaide, he worked in consultancies in Melbourne and London. He runs the development department in one of Australia's leading IT firms, as well as doing database application consultancy and training. He heads up the Adelaide SQL Server User Group, and holds several Microsoft certifications.

Rob has been involved with Microsoft technologies for most of his career, but has also done significant work with Oracle and Unix systems. His preferred database is SQL Server and his preferred language is C#. Recently he has been involved with Microsoft Learning in the US, creating and reviewing new content for the next generation of Microsoft exams.

Over the years, Rob's clients have included BP Oil, OneLink Transit, Accenture, Avanade, Australian Electorial Commission, the Chartered Institute of Personnel and Development, the Royal Borough of Kingston, Help The Aged, Unisys, Department of Treasury and Finance (Vic), National Mutual, the Bible Society and others.

Did you mean to come here? My blog is now at http://msmvps.com/blogs/robfarley



20 April 2005

Aggregations in SQL

Today a friend of mine asked me about a SQL query he was trying to write. He had expressed it in English, along the lines of: "Each record represents a cost to the organisation dependent on the value of field3. If field3 is 1, then it costs us $1.20. If it's not, then it costs us $1.50."

He had worked out that he wanted to multiply the count where field3 = 1 by 1.2, and then add that to 1.5 * the count where field3 <> 1. But got stuck.

Problem was, he expressed it as:

CASE WHEN field3 = 1 THEN 1.20 * COUNT(id) else 1.50 * COUNT(id) END

When he tried to sum this, he got in a mess, because sum and count don't really go together very well like that.

What he wanted was:

sum(case when field3 = 1 then 1.20 else 1.50 end)

Reason being that he should've concentrated on working out the cost FOR EACH RECORD. That's easy to sum, with an appropriate 'group by' clause. By trying to work it out against the count, he was just approaching the problem from the wrong direction.

Posted by Rob Farley at 5:12 pm