In Crosstab Query If Any Values Are Null I Want Sum to Be Null

In Crosstab Query If Any Values Are Null I Want Sum to Be Null

Post by mcl » Thu, 26 Feb 2004 02:36:44

I have a crosstab query of precipitation by year and month. The rows are
years and the months are columns. I have a last column of annual totals. But
in years where there is missing monthly data and therefore a null value I
want the annual to be null. Is there anyway to tell a crosstab to do that
when including sums?

In Crosstab Query If Any Values Are Null I Want Sum to Be Null

Post by Dr. Strang » Thu, 26 Feb 2004 05:05:03


By and large MS produces 'average' software.Nowhere near
'great' stuff but good enough for the user to get by.
What is truely ironic and is an enigma is that buried
within their 'average' stuff are some real software gems
that remain hidden and destined to never see the light
of day.Of course the MS user community bears some
responsibility here too as most MS users do not understand
how to think 'outside' the box.
OK, what does this have to do with the crosstab query?
It just so happens that this query encapsulates sophisticated
concepts usually illustrated by complex sql such as subqueries,
derived tables and all those convulated queries you can only
get a headache from in Joe Celko's books.But the crosstab query
has it own way of mimicking complex sql, sort of its own special
syntax.And this syntax is fairly simple once you understand it!

Now lets take this example.Below is table mcl which has
2 years of data from Jan thru Apr.But for 2004 there is
no data for Feb.

Table mcl
id year1 month1 cost
1 2003 Jan 4
2 2003 Jan 6
3 2003 Jan 5
4 2003 Feb 2
5 2003 Feb 7
6 2003 Mar 1
7 2003 Mar 4
8 2003 Apr 10
9 2003 Apr 9
11 2004 Jan 3
12 2004 Jan 7
13 2004 Mar 8
14 2004 Mar 4
15 2004 Apr 5
16 2004 Apr 3

Here is the usual xtab using SUM(cost) to
give the annual total for each year.

TRANSFORM SUM([cost]) AS [value]
SELECT [year1], SUM(cost) as Total
FROM mcl
GROUP BY [year1]
PIVOT [month1] In ('Jan','Feb','Mar','Apr');

year1 Total Jan Feb Mar Apr
2003 48 15 9 5 19
2004 30 10 12 8

But what we really want to do is show a null (nothing) for
2004 since the Feb data is missing.The crosstab query
allows all the processing to do this.

(Remove the comment lines (--) to run it).

TRANSFORM SUM([cost]) AS [value]
SELECT [year1],
-- Get the count of sums across all months for each year (mthcnt).
-- This is done by simply applying the count aggregate to the
-- [value] alias defined in TRANSFORM.mthcnt is just a working
-- value that can used to reach a required result.There no
-- limit on the number of working values you can create.
count([value]) AS mthcnt,
-- The following statement says if all months for a year have
-- data (mthcnt=4) sum the sums([value]).This gives the annual total.
-- If a year has less than 4 sums a null results.
SUM(switch(mthcnt=4,[value])) AS Total
FROM mcl
GROUP BY [year1]
PIVOT [month1] In ('Jan','Feb','Mar','Apr');


year1 mthcnt Total Jan Feb Mar Apr
2003 4 48 15 9 5 19
2004 3 10 12 8

Of course you can hide mthcnt or get rid of it subsequently.

This is just the tip of the iceburg of what's buried in the
crosstab query.Similar gems are buried in Sql Server:).

For crosstabs and much more on S2k check out RAC.
Free administration and query tool for all S2k version - QALite.


In Crosstab Query If Any Values Are Null I Want Sum to Be Null

Post by mcl » Fri, 27 Feb 2004 06:17:56

didn't do it your way but it got me thinking. I did it with an IIF

IIf(Count([month])=12,Sum([sumofprecipin]),Null) AS Annual
FROM PRECIPinYearMonth INNER JOIN Months ON PRECIPinYearMonth.Mo =
PIVOT Months.month In

Works great.

"Dr. StrangeLove" < XXXX@XXXXX.COM > wrote in message
news:ec4wXHx% XXXX@XXXXX.COM ...


In Crosstab Query If Any Values Are Null I Want Sum to Be Null

Post by Dr. Strang » Sat, 28 Feb 2004 01:33:15

Well I'm glad you found a solution.
But your solution can only work if you have a single value for
each month.The solution I presented is independent of the
number of observations for each month.It works off the sums
computed for each month.Big difference:)

RAC v2.2 and QALite @

In Crosstab Query If Any Values Are Null I Want Sum to Be Null

Post by mcl » Sun, 29 Feb 2004 03:26:19

The query I have that feeds my crosstab query generates a single total
precip amount for each year/month for the station (BLKSTN) in question. BTW,
the database has months as just 1 through 12. I use a little lookup table
(Months) with Parameters mo (1-12), and Month (Jan through Dec) to give me
the Jan through Dec in the crosstab output.