How to merge the summary with details in the same table

Hello Experts,

I want help in preparing a specialize report and I want to manipulate and prepare it with SQL query so only few tweaks are needed in reporting tool.

Please refer to the attachment.

Thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
You forgot to attach it!
0
JimiJ13I T ConsultantAuthor Commented:
Sorry Paul! Please refer to the attachment.

Thanks.
GroupingSum-Detail.xlsx
0
PortletPaulfreelancerCommented:
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").
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

PortletPaulfreelancerCommented:
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 |

Open 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

Open in new window

0
JimiJ13I T ConsultantAuthor Commented:
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.
0
JimiJ13I T ConsultantAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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 |

Open 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.
0
PortletPaulfreelancerCommented:
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?
0
PortletPaulfreelancerCommented:
| GRP | TYPE |     ORNUM |          DESCRIPTION | AMOUNT |      X |
|-----|------|-----------|----------------------|--------|--------|
|   A |   TI |    (null) |               (null) |  14495 | (null) |
|   A |   LO | 358996292 |               (null) |      1 | (null) |
|   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 | (null) |

Open in new window

select
        GRP
      , Type
      , ORNUM
      , Description
      , Amount
      , case when count(*) over(partition by Type) > 1 then 
                    sum(Amount) over(partition by Type)
        end as x
from (
      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)         as ORNUM
            , case when Type = 'TI' then null else Description end       as Description
            , sum(Amount)                                                as 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
     ) as derived
order by
        grp
      , Type DESC

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
Great! I will try this and let you know.
0
JimiJ13I T ConsultantAuthor Commented:
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!
0
PortletPaulfreelancerCommented:
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
0
JimiJ13I T ConsultantAuthor Commented:
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!
0
JimiJ13I T ConsultantAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
no problem,

Glad to hear you are doing those things in Telerik because SQL isn't good at those.
0
JimiJ13I T ConsultantAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.