List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 2 2007 8:47pm
Subject:Re: ROLLUP and Text on total lines
View as plain text  
In the last episode (May 02), Jesse said:
>  Using ROLLUP in my GROUP BY, I have been able to get MySQL to insert 
>  sub-total lines, which is great.  However, it would be even better if I 
>  could determine the text on those subtotal lines.  Here's my query:
>  SELECT Sc.State, St.Description, Count(*)
>  FROM InvHead I
>    JOIN Schools Sc on Sc.ID=I.ChapterID
>    JOIN Participants P ON P.InvNo=I.InvNo
>    JOIN StatusCodes St ON P.Status=St.Code
>  GROUP BY Sc.State, St.Description WITH ROLLUP
>  I have seen examples that use (I believe) MS SQL and the Grouping()
>  function to determine if the row is an inserted subtotal or total
>  row.  Is there a way to determine if the row is a subtotal or total
>  row, and change the text to indicate which it is?

The totals rows should have some of the grouped fields set to NULL, at
least according to the examples in the manual.  You should be able to
test those in an IF() expresion that returns a special string if State
and/or Description are NULL.
	Dan Nelson
ROLLUP and Text on total linesJesse2 May
  • Re: ROLLUP and Text on total linesDan Nelson2 May