0

<table class="demo">
    <caption>Sample Data</caption>
    <thead>
    <tr>
        <th>Acct</th>
        <th>Type</th>
        <th>Amount</th>
        <th>Total</th>
    </tr>
    </thead>
    <tbody>
    <tr>
        <td>&nbsp;101</td>
        <td>&nbsp;H</td>
        <td>&nbsp;0</td>
        <td>&nbsp;111</td>
    </tr>
    <tr>
        <td>&nbsp;102</td>
        <td>&nbsp;H</td>
        <td>&nbsp;0</td>
        <td>&nbsp;106</td>
    </tr>
    <tr>
        <td>&nbsp;103</td>
        <td>&nbsp;R</td>
        <td>&nbsp;10</td>
        <td>&nbsp;106</td>
    </tr>
    <tr>
        <td>&nbsp;104</td>
        <td>&nbsp;R</td>
        <td>&nbsp;20</td>
        <td>&nbsp;106</td>
    </tr>
    <tr>
        <td>&nbsp;105</td>
        <td>&nbsp;R</td>
        <td>&nbsp;30</td>
        <td>&nbsp;106</td>
    </tr>
    <tr>
        <td>&nbsp;106</td>
        <td>&nbsp;T</td>
        <td>&nbsp;0</td>
        <td>&nbsp;111</td>
    </tr>
    <tr>
        <td>&nbsp;107</td>
        <td>&nbsp;H</td>
        <td>&nbsp;0</td>
        <td>&nbsp;110</td>
    </tr>
    <tr>
        <td>&nbsp;108</td>
        <td>&nbsp;R</td>
        <td>&nbsp;15</td>
        <td>&nbsp;110</td>
    </tr>
    <tr>
        <td>&nbsp;109</td>
        <td>&nbsp;R</td>
        <td>&nbsp;25</td>
        <td>&nbsp;110</td>
    </tr>
    <tr>
        <td>&nbsp;110</td>
        <td>&nbsp;T</td>
        <td>&nbsp;0</td>
        <td>&nbsp;111</td>
    </tr>
    <tr>
        <td>&nbsp;111</td>
        <td>&nbsp;T</td>
        <td>&nbsp;0</td>
        <td>&nbsp;</td>
    </tr>
    <tbody>
</table>

Data Set

Suppose I have data as shown in this table. I want sum to be calculated in total accounts by referring Total Into column. Also, total accounts have their own higher-level linking where they should be summed up. Can this be achieved in SQL server query? If not, then can someone guide me with SSRS expression to achieve this?

I tried to achieve this with multiple nested queries, partition by clause, etc. Then I switched to SSRS and tried a few expressions. But now I'm kind of stuck

Omy
  • 1
  • 2
  • I think you are looking for [Groupping Sets](https://www.sqlservertutorial.net/sql-server-basics/sql-server-grouping-sets/) – Anel Hodžić Oct 26 '22 at 11:46
  • @AnelHodžić: Can you guide me with a sample query? Let's assume column names are acct, type, amt & total – Omy Oct 26 '22 at 11:50
  • 1
    *Consumable* sample data (not an image), expected results, and a clearer description would really help us help you here. – Thom A Oct 26 '22 at 11:52
  • As Larnu commented. We could really use sample data and expected result. – Anel Hodžić Oct 26 '22 at 11:53
  • Hey ! I don't think I undertood what you realy want to achieve, could you give the expected output depending on what you have in your example ? – Delta Oct 26 '22 at 11:56
  • 2
    There is no way I am going to click a link to a file sharing site (I, and many other users here are *far* too aware of how foolish it is to download files from a complete stranger); the information needs to be included in the question. – Thom A Oct 26 '22 at 12:07
  • @Larnu: I can completely understand your concern. I will edit the question – Omy Oct 26 '22 at 12:17
  • Have updated the question with sample data. Expected output is as shown in image – Omy Oct 26 '22 at 12:40
  • So is your data actually XML in your table? – Thom A Oct 26 '22 at 12:55
  • No. The sample data I shared is coming from different tables and I managed to get the sum of regular accounts. But total accounts are still 0. Basically, I need to design a report like Trial Balance or Budget Comparison where we usually have certain set of accounts clubbed in one section like Assets or Liabilities. Their individual sum and then sum of all total accounts. – Omy Oct 26 '22 at 13:10
  • If what you are trying to do is say "When Acct Type = Total then sum Amount where the Acct Code matches the Total Into Acct column" then you could do a `lookupset` to get the values and a bit of custom code to sum them. See this answer for more details https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/36143175#36143175 – Alan Schofield Oct 26 '22 at 13:33
  • Please see [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question) and [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). We'll need to see some sample data as a table, not an image, and your expected output. As well as what your current query is/what you've tried. – griv Oct 26 '22 at 14:23

0 Answers0