Tutorial :MySQL - show field value only in first instance of each grouped value?



Question:

I don't think this is possible, but I would like to be proved otherwise.

I have written a simple report viewing class to output the results of various database queries. For the purpose of improving the display, when I have a report with grouped data, I would like to display the field value only on the first row of each unique value - and I would like to do this at the query level, or it would necessitate additional logic in my class to determine these special values.

It will probably help to illustrate my requirements with a simple example. Imagine this dataset:

Year    Quarter    Total  2008    Q1         20  2008    Q2         25  2008    Q3         35  2008    Q4         40  2009    Q1         15  2009    Q2         20  2009    Q3         30  2009    Q4         35  

If possible, I would like the dataset returned as:

Year    Quarter    Total  2008    Q1         20          Q2         25          Q3         35          Q4         40  2009    Q1         15          Q2         20          Q3         30          Q4         35  

Is there any way of doing this progammatically in MySQL?


Solution:1

SELECT  CASE WHEN @r = year THEN NULL ELSE year END AS year,          quarter,          total,          @r := year  FROM    (          SELECT  @r := 0          ) vars,          mytable  ORDER BY          year  

@r here is a session variable. You can use these in MySQL like any variable in any procedural language.

First, it's initialized to zero inside the subquery.

Second, it's checked in the SELECT clause. If the current value of @r is not equal to year, then the year is output, else NULL is output.

Third, it's updated with current value of year.


Solution:2

Why would you want to do this? What about existing records where the Year column is empty or null?

Beautifying the output belongs inside the report logic. In pseudocode it would be sth. like:

var lastYear = 0  foreach (record in records)  {     if (record.Year == lastYear)     {       print "   "     }     else     {       print record.Year       lastYear = record.Year     }       // print the other columns   }  


Solution:3

Not the answer you asked for, but...

Sounds like an iffy thing to be doing in MySQL in the first place. Just looking at the raw rows of data, 2008 and 2009's Q2s don't seem to make much sense as data rows. The issue is presentational, not a matter of fetching data. Sounds more like something to be written into your viewing class - when passed a certain parameter, for example, it will know not to repeat things like "2008".

This allows for greater reusability of code, as well: rather than rewriting the query when you want to present the data differently, say by quarters rather than be year, you can just change one of the arguments of the viewing class so that the same query with a different order clause can output:

Quarter   Year   Total  Q1        2008   20            2009   15  Q2        2008   25            2009   20  ...  


Solution:4

It does not exactly match your request but I would rather pivot my table. It allows to visually compare figures from the 2 years as you have one quarter per column:

SELECT Year,          SUM(IF(Quarter="Q1", Rev, 0)) AS Q1,         SUM(IF(Quarter="Q2", Rev, 0)) AS Q2,          SUM(IF(Quarter="Q3", Rev, 0)) AS Q3,          SUM(IF(Quarter="Q4", Rev, 0)) AS Q4   FROM t1 GROUP BY 1  ORDER BY 1  

You then have:

YEAR Q1   Q2   Q3   Q4  2008  2009   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »