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

  • Thread starter Thread starter mcl
  • Start date Start date
M

mcl

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?
 
Greetings,

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');

Result:

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.
Visit:
www.rac4sql.net
 
I didn't do it your way but it got me thinking. I did it with an IIF
statement:

TRANSFORM Sum(PRECIPinYearMonth.SumofPRECIPin) AS SumOfSumOfPRECIPin
SELECT PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year,
IIf(Count([month])=12,Sum([sumofprecipin]),Null) AS Annual
FROM PRECIPinYearMonth INNER JOIN Months ON PRECIPinYearMonth.Mo = Months.mo
GROUP BY PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year
PIVOT Months.month In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Works great.

Dr. StrangeLove said:
Greetings,

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');

Result:

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.
Visit:
www.rac4sql.net
Click to expand...
 
mcl said:
I didn't do it your way but it got me thinking. I did it with an IIF
statement:

TRANSFORM Sum(PRECIPinYearMonth.SumofPRECIPin) AS SumOfSumOfPRECIPin
SELECT PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year,
IIf(Count([month])=12,Sum([sumofprecipin]),Null) AS Annual
FROM PRECIPinYearMonth INNER JOIN Months ON PRECIPinYearMonth.Mo = Months.mo
GROUP BY PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year
PIVOT Months.month In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Works great.
Click to expand...

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 @
www.rac4sql.net
 
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.

Dr. StrangeLove said:
mcl said:
I didn't do it your way but it got me thinking. I did it with an IIF
statement:

TRANSFORM Sum(PRECIPinYearMonth.SumofPRECIPin) AS SumOfSumOfPRECIPin
SELECT PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year,
IIf(Count([month])=12,Sum([sumofprecipin]),Null) AS Annual
FROM PRECIPinYearMonth INNER JOIN Months ON PRECIPinYearMonth.Mo = Months.mo
GROUP BY PRECIPinYearMonth.BLKSTN, PRECIPinYearMonth.Year
PIVOT Months.month In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Works great.
Click to expand...

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 @
www.rac4sql.net
Click to expand...
 
Back
Top