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: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:
iif(test,value_if_true,value_if_false)Wrap that in a sum, and you count the record only if the value is true:
sum(iif(test,1,0))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],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.
count(*) AS Total
FROM data
GROUP BY yr;
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.