Re: Help with missing data in query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance



"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: Need help with hint syntax in SQL Server 2000
    ... unable to recognize that it shoul push the where clause condition down ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ... Thank you both for the replies. ...
    (comp.databases.ms-sqlserver)
  • Re: Multiplying numeric(19,4) Values
    ... in the context of my original query: ... I don't think I suggested that in the context of this query. ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.programming)