[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to merge the summary with details in the same table

Posted on 2014-08-08
16
Medium Priority
?
90 Views
Last Modified: 2014-08-12
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.
0
Comment
Question by:JimiJ13
  • 8
  • 8
16 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40250263
You forgot to attach it!
0
 

Author Comment

by:JimiJ13
ID: 40250281
Sorry Paul! Please refer to the attachment.

Thanks.
GroupingSum-Detail.xlsx
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40250298
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40250306
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
 

Author Comment

by:JimiJ13
ID: 40250334
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
 

Author Comment

by:JimiJ13
ID: 40254739
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254768
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254772
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40254784
| 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
 

Author Comment

by:JimiJ13
ID: 40254792
Great! I will try this and let you know.
0
 

Author Comment

by:JimiJ13
ID: 40254904
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254910
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
 

Author Comment

by:JimiJ13
ID: 40254988
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
 

Author Comment

by:JimiJ13
ID: 40254999
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40255037
no problem,

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

Author Comment

by:JimiJ13
ID: 40255125
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question