Tuesday, June 26, 2012

MS Access SQL tip: sum, iif and datepart

I've been working for a while on a large data set for a future story, and I created a query that I thought different enough to share.

sum, iif and datepart

I have a series of records that have a date in mm/dd/yyyy format. I wanted to know how many records there were for each month of a 3-year span, so I wanted to query and group by year, but I wanted a count of the records by month. I wanted it to look like this:

Units By Month
YR Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
2009 89 75 92 72 97 75 112 122 112 93 82 96 1117
2010 113 102 105 116 116 122 149 126 113 109 118 115 1404
2011 144 107 143 149 152 134 142 158 181 139 134 174 1757

Thanks to a GeekAustin class on writing SQL, I had a way.

I used the MS Access iif function to count and sum a record if it met a certain condition, but to not count it if it didn't. In this case I was checking if it was the proper month for that column. This is the iif function:

Wrap that in a sum, and you count the record only if the value is true:

So, I use the datepart function to test the record for every month:

SELECT yr,sum(iif(datepart("m",DATE_OCCURRED) = 1,1,0)) AS Jan, sum(iif(datepart("m",DATE_OCCURRED) = 2,1,0)) AS Feb, sum(iif(datepart("m",DATE_OCCURRED) = 3,1,0)) AS Mar, sum(iif(datepart("m",DATE_OCCURRED) = 4,1,0)) AS Apr, sum(iif(datepart("m",DATE_OCCURRED) = 5,1,0)) AS May, sum(iif(datepart("m",DATE_OCCURRED) = 6,1,0)) AS Jun, sum(iif(datepart("m",DATE_OCCURRED) = 7,1,0)) AS Jul, sum(iif(datepart("m",DATE_OCCURRED) = 8,1,0)) AS Aug, sum(iif(datepart("m",DATE_OCCURRED) = 9,1,0)) AS Sep, sum(iif(datepart("m",DATE_OCCURRED) = 10,1,0)) AS Oct, sum(iif(datepart("m",DATE_OCCURRED) =11,1,0)) AS Nov, sum(iif(datepart("m",DATE_OCCURRED) = 12,1,0)) AS [Dec],
count(*) AS Total
FROM data
The ""iif" is the cool part, but it's the "datepart" function that makes it powerful in this instance, as I can test if the month is true. I test if "m" (or month) equals "1" (or January.) If so, make the value 1 so it will be summed in that column. Otherwise, the value is zero and it is not added to the sum.

A big thanks to Boyd Hemphill of that GeekAustin class for that bit about summing the dates. It's gratifying to actually use code out of a class like that.


Christian McDonald 5:58 PM  

Took this one a bit further today by adding an EVAL statement to the IIF so two conditions had to be true to be counted.

sum(iif(EVAL(datepart("yyyy",DATE_FIELD) = 2005 AND datepart("m",DATE_FIELD) = 9),1,0)) AS [Sept 2005]

  © Blogger template 'A Click Apart' by Ourblogtemplates.com 2008

Back to TOP