Re: Help with missing data in query

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
.

Relevant Pages

  • Re: Help with missing data in query
    ... Change the last par tof the query to ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (microsoft.public.sqlserver.mseq)
  • RE: Combining Similar Rows
    ... I would suggest first creating a query to get a rollup title for the like ... Put all of the rest of the columns in the query as well. ... Do sums, group by the ... > Can anyone help with the SQL? ...
    (microsoft.public.access.queries)
  • Re: move is null
    ... so the totals show up at the top of the table. ... Try to sort the query: ... SELECT ProductID, SUMAS UnitPrice ... GROUP BY ProductID WITH ROLLUP ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Help with missing data in query
    ... I did go to that site But I couldn't figure out how to upload anything. ... Try changing the ORDER BY clause to read either ... GROUP BY m.MonthName WITH ROLLUP ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Combining Similar Rows
    ... Are you trying to do the row totals in the same query as the account name ... > I can't get the sum to work correctly across the rows. ... >> I would suggest first creating a query to get a rollup title for the like ...
    (microsoft.public.access.queries)