Re: Help with missing data in query



"Hugo Kornelis" wrote:

On Wed, 15 Aug 2007 17:02:04 -0700, SLIMSHIM wrote:

i"m including my final code it has one bug i can't get the sort order
straight
i.e. jan feb mar .......Total
it comes in alphabeticaly

Hi slimshim,

That's because you asked it to order on the month name column :-)

Change the last par tof the query to

GROUP BY m.MonthName WITH ROLLUP
ORDER BY MIN(m.MonthId)

I was first about to suggest to include MonthId in the GROUP BY, but I'm
not sure if the WITH ROLLUP option likes that. The workaround I chose is
to use an aggregate function for the ORDER BY.


If that doesn't work (you didn't follow the instructions I linked to
that would have enabled me to test before posting), then change the end
to

GROUP BY m.MonthID WITH ROLLUP
ORDER BY m.MonthID

and change the first line to read

SELECT MAX(COALESCE(m.MonthName, 'Total')) AS MONTH,

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


THanx for the help
I did go to that site But I couldn't figure out how to upload anything.


after i tried your suggested changed this is what my result looks like
Jan 0 0 0
Total 12 0 12
Feb 0 0 0
Mar 0 0 0
Apr 0 0 0
May 0 0 0
Jun 0 0 0
Jul 12 0 12
Aug 0 0 0
Sep 0 0 0
Oct 0 0 0
Nov 0 0 0
Dec 0 0 0

Total falls under jan not dec as it should.
I appreciate all your help.

I was wondering if I could use COMPUTE intead of coalese to get the total
heading to the bottom ?

THanx again
SLIMSHIM
.

Relevant Pages

  • Re: Help with missing data in query
    ... I did go to that site But I couldn't figure out how to upload anything. ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Help with missing data in query
    ... Change the last par tof the query to ... GROUP BY m.MonthName WITH ROLLUP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.mseq)
  • Re: SQL Humor
    ... Hugo Kornelis wrote: ... SQL Server will often produce the same execution for both versions. ... the big questions for the RM, *for me* seem to include view updatability which has to do with the operators of the algebra as well as whether a relational engine can implement customary features such as concurrency control and presentation coherence without being written in a language that eschews the relational operators. ...
    (comp.databases.theory)
  • Re: How to Gnerate a Random ID Number
    ... formula with a seed as input to get at a pseudo-random value?) ... that definitely rules out newid() as a "good" pseudo random number ... this on SQL Server 2005, and the seed does not seem to be reset (or at ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Not passing back values on an if statement
    ... Hugo Kornelis wrote: ... SQL Server has to check not only that a row ... > column Application doesn't allow NULLs, ... This email account is my spam trap so I ...
    (microsoft.public.sqlserver.programming)