Databases
StackOverflow2013
Postsdbo
POSQL Is it possible to replace values in a Rollup?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Is it possible to replace values in a Rollup?
    primarykey
    data
    text
    <p>I have a result set that mainly consists of columns of values. However, the left most column contains text which I'm using to effectively label each row. When I ROLLUP, the values total, which is great. The text in my 'No KS2' column rolls up to a NULL which is expected. Is there anything I can do to replace the NULL with text eg 'Total'?</p> <p>All the NULLs in the other columns should be there and I don't need to replace those.</p> <p>Here's an example of my results set eg:</p> <pre><code>No KS2 6c/D- 6b/D 6a/D+ 2a 1 NULL NULL 3c 3 NULL NULL 3b NULL 5 NULL 3a NULL NULL 6 NULL 4 5 6 </code></pre> <p>Here's what I'd like to do:</p> <pre><code>No KS2 6c/D- 6b/D 6a/D+ 2a 1 NULL NULL 3c 3 NULL NULL 3b NULL 5 NULL 3a NULL NULL 6 Total 4 5 6 </code></pre> <p>EDIT:</p> <p>When generating the KS2 column results set using the group by I have already replace NULL values with 'No KS2'. Please see my GROUP BY code below:</p> <pre><code>GROUP BY CASE Name WHEN 'English' THEN CASE WHEN [Ks2en] IS NULL OR [Ks2en]='' THEN 'No KS2' WHEN [Ks2en] IN ('1a','1b','1c') THEN '1' ELSE [Ks2en] END WHEN 'Mathematics' THEN CASE WHEN [Ks2ma] IS NULL OR [Ks2ma]='' THEN 'No KS2' WHEN [Ks2ma] IN ('1a','1b','1c') THEN '1' ELSE [Ks2ma] END ELSE CASE WHEN [Ks2av] IS NULL OR [Ks2av]='' THEN 'No KS2' WHEN [Ks2av] IN ('1a','1b','1c') THEN '1' ELSE [Ks2av] END END WITH ROLLUP </code></pre> <p>EDIT: This isn't a duplicate as the solution to the question that this is supposed to be a duplicate of does not resolve my issue. I edited the code to explain the difference. I already have a case statement that replaces out my NULL values in the KS2 column with 'No KS2'.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatt
    1. USMatt
    plurals
    1. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYes, along the right lines. However, I already have a case statement that replaces out my NULL values in the KS2 column with 'No KS2'. I'll post my GROUP BY code.
      singulars
      1. POSQL Is it possible to replace values in a Rollup?
      1. USMatt
    2. COEncase the whole lot in parenthesis and create a select statement which replaces the NULL. select * FROM (<your full query>) data
      singulars
      1. POSQL Is it possible to replace values in a Rollup?
      1. UStwoleggedhorse
    3. CORegarding your edit. It **is** a duplicate. The correct way of replacing `ROLLUP` values is to use the `GROUPING` function. Using `ISNULL` or `COALESCE` will incorrectly apply the total label to groups where the grouping value is `NULL` as well. As you haven't bothered supplying a SQL Fiddle I won't bother showing you how to apply `GROUPING` to your situation (code too long for a comment and I certainly have no interest in setting up a Fiddle from scratch)
      singulars
      1. POSQL Is it possible to replace values in a Rollup?
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload