Sunday, April 21, 2013

Thoughts from ISOJ 2013

First, let me say that Rosental and Amy put on a fabulous conference, yet again. Great topics, fantastic speakers, and everything else worked.

I'm not going to do a deep dive on this ... I just want to throw out a few thoughts on a few of the subjects.

Chris Gilbert was brilliant in his discussion about how the Deseret News followed the Clay Christensen model of disruptive innovation to not only survive, but thrive. They split off their digital business from the legacy print business, to give it independence and flexibility to do what was necessary to grow. (Big over simplification, but heh.)

Following that was more panels on disruption, with David Skok of, Jennifer Carroll of Gannett digital, Jim Brady of Digital First and Jim Maroney of Belo and the Dallas Morning News.

For me, I related most to Jim Brady with the Thunderdome project (name alone wins my heart!), which is essentially shared services for digital, but it sounds like they did it right. Would really like to hear the tale from a foot soldier inside that org. Maroney was almost a polar opposite of Gilbert from the previous panel, touting new business ventures to prop up the not-flattening losses for the print and digital mediums. {sigh}

Andy Carvin's talk on social media was definitely on point, as that morning he had been working his social media mojo to find what was "most probably" Dzhokar Tsarnaev's Twitter account. There is a lot of talk about incorrect tweets, scanner traffic, reddit, and the word "confirmed." At points I wanted to shout "Preach it!".

This was my second time to hear Trei Brundrett of Vox Media (SB Nation) talk about their development efforts, this time focusing on responsive design. I was (and am) enamored with their "living story" idea, and tried to talk my company into something like that a couple of years ago, but we didn't have time to devote that development effort. Of course, Vox Media redesigned and recoded 310 sites in 6 months, but hey ... whatever. Miranda Mulligan talked about her experience at the Boston Globe, who introduced one of the first major responsive news sites. Michael Donohoe talked about Quartz, and the Texas Tribune's Travis Swicegood talked about his thoughts how responsive design should be about more than changing width and device, and more about the person viewing the content.

Lots and lots of fun. I really like listening to these technical panels and the challenges these guys have faced and conquered. Makes me feel a little small that I'll never be in that league, but I guess I have a different value in this world. I hope.

Day two started off with Emily Bell, formerly of The Guardian. I was a little more interested in the next talk on mobile journalism, where Ivo Burum and Alisa Richardson both talked about their incredibly inspiring work to get mobile journalism into the hands of others who don't normally have the opportunity. Representatives from three heavyweights, Chicago Tribune (Chris Courtney), The Wall Street Journal (David Ho) and the Washington Post (Joey Marburger) discussed their mobile strategy and development efforts. Courtney's talk hit home as he talked about development mistakes that I've helped my company commit in past and present. All three of these guys are brilliant.

The data visualization talk was both inspiring and disheartening, as there was some really good work shown and I would like to live up to that. Alberto Cairo's talk on the quality of graphic presentation really had me thinking that I need to make sure I have a little more theory of good design in my next dataviz class. Following was one of the graphic editors in Snow Fall, an excellent piece of "immersive storytelling" that earned a Pulitzer. More inspiring work by Propublica's Scott Klein and Periscope's Kim Rees.

Jill Abramson of the NYTimes closed it out for me (I didn't stay for the research papers) and it was cool to hear she was in the newsroom most of Friday night when the 2nd Boston bombing suspect was caught. She does run an incredible staff (four Pulitzer's don't lie) and she is sure to give the credit for it.

My best discussions of the weekend, as usual, came outside of the actual conference. I got a lot of great higher education insight from Mindy McAdams (University of Florida, Patrick Howe (Cal Polytechnic), Jonathan Groves (Drury) and of course, Cindy Royal of Texas State.

There was so much going on, and I didn't capture any of it very well. Made me feel good to be journalist in this day and age, and I hope to live up to the standards folks on the panels and in the audience set every day. Awesome.

CLARIFICATION: I had described Chris Courtney, David Ho and Joey Marburger all as developers, and that was an oversimplification and really just wrong. Their titles listed are:  Chris Courtney, mobile product manager, Tribune Company; David Ho, editor of mobile, tablets and emerging technology, The Wall Street Journal; Joey Marburger, mobile design director, Washington Post.

Saturday, April 06, 2013

Using calculations in SQL statements

I've not done much training with Microsoft Access, so I've been learning by doing and trying, for the most part. This week, I figured out that you can do a calculation in SELECT statement, like to figure out the percentage of records in your selection. Up to now, I've pulled grouped results into Excel and did my calculations there.

I would think there would be an even easier way to build this, but ... maybe there is if I knew how to use the Query Wizard instead of writing my own SQL.

SELECT AASStatus, COUNT(*) AS [COUNT], FORMAT((COUNT(AASStatus) / (SELECT COUNT(*) FROM [DC-Match-N])),"Percent") AS [Percent]
FROM [DC-Match-N]

So, my table name (actually a query) is [DC-Match-N], and I want to group the all the records by their AASStatus, count the records and get the percentage. Probably my most common goal is to find out how many records meet a certain search criteria.

Breaking down the SELECT LINE:

  • First is the field I want to count.
  • Next is the count of records for that row. Since I GROUP BY AASStatus later, it will count the records that match each status. I aliased this column as [Count]
  • Next is where we do the percentage:
    • FORMAT allows me to format a result as a percentage, currency, etc. I learned about it in this post on (I actually did this first by doing my own *100 and using the ROUND function, but found the FORMAT function later.
    • Inside of the FORMAT tag is the math. Put simply is it "[part] / [whole]" formatted as a percent.
      • The [part] is the COUNT(AASStatus), or the count of the records that matches the AASStatus for that row.
      • The [whole] is a new SELECT statement to get the count of all records from the [DC-Match-N] query.
      • The percentage part is handled by the FORMAT function above.

Here's what it looks like:

AASStatus Count Percent
Status 1 136 4.63%
Status 2 39 1.33%
Status 3 5 0.17%
Status 4 190 6.46%
Status 5 2569 87.41%

This seems so simple ... to do the percentage as part of the select, but I hadn't thought of trying it before. 

  © Blogger template 'A Click Apart' by 2008

Back to TOP