How to merge the summary with details in the same table

You forgot to attach it!

Sorry Paul! Please refer to the attachment.

Thanks.
GroupingSum-Detail.xlsx

Thanks.

However I don't understand it without your assistance.
e.g.
Where does the column [GRP] comes from?
There is nothing apparent in the spreadsheet for this.

What is the relevance of the question title?
"How to merge the summary with details in the same table"

(The results in the spreadsheet don't indicate a merge)

-----
Do you REALLY have a field called "OR"????? That's not good (It's a "reserved word").

Don't understand GRP or Description

This is as close as I can get, without more information:

| GRP | TYPE |  COLUMN_2 |          DESCRIPTION | COLUMN_4 |
|-----|------|-----------|----------------------|----------|
|   A |   TI |    (null) |               (null) |    14495 |
|   A |   LO | 358996292 |               (null) |        1 |
|   A | Dona | 358996294 |                 Jose |        1 |
|   A | Dona | 358996296 |                 Juan |        1 |
|   A | Dona | 358996295 |                Pedro |        1 |
|   B |  PTh |    504323 | Recovery of Expenses |     1200 |
|   B |  Pth |    504313 |            Return CA |       50 |
|   B |   II | 327040866 |                  Net |        1 |

Select allOpen in new window

produced by this query:

select
        case when Type in ('TI','Dona','LO') then 'A' else 'B' end as GRP
      , Type
      , max(case when Type = 'TI' then null else [OR] end)
      , case when Type = 'TI' then null else Description end as Description
      , sum(Amount)
from sample
group by
         case when Type in ('TI','Dona','LO') then 'A' else 'B' end
      ,  Type
      , case when Type = 'TI' then null else Description end
order by
        grp
      , Type DESC

http://sqlfiddle.com/#!3/f679a/9

Select allOpen in new window

Hi Paul,

The GRP is a new grouping to be created and you've done correctly.
ORNum is the actual name of OR.

Merge - might not be the right terms but I think you made a very bright assumption.

Right now, I'm in difficulty using solution to the actual query but it's getting better using sub-view (Sample - view).

I'm hoping to do more tweaks to get what I want.

Thanks.

Hi Paul,

For my accounting report presentation condition, kindly help me come up with column 5 as a repetitive subtotal of Type (Dona or  PTh) as the only Types with probable subtotals as shown:

| GRP | TYPE |  COLUMN_2 |          DESCRIPTION | COLUMN_4 | Col_5
|-----|----------|------------------|-------------------------------|------------------|-----------|
|   A |   TI      |            (null) |                             (null) |          14495 |              |
|   A |   LO    | 358996292 |                             (null) |                   1 |              |
|   A | Dona | 358996294 |                               Jose |                   1 |           3 |
|   A | Dona | 358996296 |                               Juan |                  1 |           3 |
|   A | Dona | 358996295 |                            Pedro |                  1 |           3 |
|   B |  PTh   |    504323    | Recovery of Expenses |           1200 |      1250|  
|   B |  Pth    |    504313    |                     Return CA |                50 |      1250|  
|   B |   II      | 327040866 |                                 Net |                  1 |               |

Thanks.

so, the same as my result plus one new unexplained column?

| GRP | TYPE |  COLUMN_2 |          DESCRIPTION | COLUMN_4 | called_what?
|-----|------|-----------|----------------------|----------|------
|   A |   TI |    (null) |               (null) |    14495 |
|   A |   LO | 358996292 |               (null) |        1 |
|   A | Dona | 358996294 |                 Jose |        1 |    3
|   A | Dona | 358996296 |                 Juan |        1 |    3
|   A | Dona | 358996295 |                Pedro |        1 |    3
|   B |  PTh |    504323 | Recovery of Expenses |     1200 | 1250
|   B |  Pth |    504313 |            Return CA |       50 | 1250
|   B |   II | 327040866 |                  Net |        1 |

Select allOpen in new window

what is the rationale? (the logic? the business rule?) for this new column

does it use NULL or empty string?
will it only have 3 and 1250 as values?

You need to communicate the required logic of this new column.

I trust this will be the only additional request against this question.

Is it a sum is column_4 ?
but only for some rows ? why some rows and not others?

what reporting tool are you using to produce the report?

Great! I will try this and let you know.

I've requested that this question be closed as follows:

Accepted answer: 0 points for JimiJ13's comment #a40254792

for the following reason:

Perfect. Thanks!

great, thanks.

as you could see by by ramblings, once you provide an expected result we can generally find a way

but it would have been useful to specify that the new column was a total - believe it or not I didn't see that and was think it was a code (doh!) of some sort... - so in the interests of volunteer time saving, please do provide any hints you can about what you are asking for,

cheers, Paul

Hi Paul,

what is the rationale? (the logic? the business rule?) for this new column
 ---  The additional column is a SubTotal that I will use to validate with the amount whether to show or not (Amount, Total or Description)   / to indent or not.

does it use NULL or empty string?
---  Null will be OK.

will it only have 3 and 1250 as values?
---  I actually use this "over(partition by Type) > 0" for I need all the SubTotals

You need to communicate the required logic of this new column.
--- Yes it great for decision making manipulations.
I trust this will be the only additional request against this question.
That's right.


Many Thanks!

Hi Paul,

I am using a Telerik reporting tool where I thought giving the subtotal along provides so much flexibility like hiding repetitive column, group subtotal, line amount, check if single line only, set indention and more....

It did and Thanks for your help.

no problem,

Glad to hear you are doing those things in Telerik because SQL isn't good at those.

We need to find ways to complement the tools we use to obtain what we want since changing the tool is not only costly and even not the best solution for all projects requirements but the resourcefulness and initiative of the user.  

Thank you & the whole Expert Exchange community as one of the best resources.