[thelist] [SQL] Can I Do This?

Matt Warden mwarden at gmail.com
Mon Jun 6 16:42:50 CDT 2005


On 6/6/05, Jacques Capesius <Jacques.Capesius at compellent.com> wrote:
> 1) There are over 20 distinct values for subjects, with the likelihood
> for there being added a lot more, so I fear an approach like this would
> have problems scaling.
> 2) There is a strong potential of subjects being added/deleted/changed
> without my knowing, which could break the query without me initially
> being aware.
> Any thoughts / comments on how to tackle these two issues? One idea I
> had was to group by month, and then by subject, which would allow me to
> sum things just fine, but it would be harder to feed the resultset into
> a graph object, which is one of my goals.

Regardless of whether you do the application programming on the SQL
itself or on the resultset, you're gonig to have to do the exact same
thing. I would personally let the database do the work, and operate on
the SQL.

Here's what I mean: You are suggesting that you return the result set
grouped or ordered by month and then subject. In this instance you are
going to need N variables (or, more likely, an array of size N) to
handle the sums of the N subjects. You will need to loop over the
entire resultset and increment the appropriate counters.

The alternative is to select the subjects in one query and then
dynamically add the CASE statements to the second query's SQL. Then
execute this second query. I would prefer this method as it seems more
elegant, and I highly suspect it is faster. You could do this in a
stored procedure, possibly; but I would just do it in whatever
language you're using for the application code (PHP, etc.).

Just my 2 cents.

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list