CODEWITHSUNDEEP
sql-serverdevexpressdashboard
Jerome Viveiros
Jerome Viveiros

Reputation: 241

MS SQL SUM one field based on the value of another field and exclude values lower than N percentage

This is probably a dumb question but SQL is not my strong point.

I have some data that is already aggregated, which I am using to display as a pie chart with drill down based on arguments on a ASPxDashBoardViewer. My problem is that sometimes the data may display some chart items with a value of 0%, which are then unclickable.

The table returned by the stored proc looks like this:

DECLARE @Responses TABLE
(
    Code varchar(10),
    Description varchar(255),
    [Status Code Count] int,
    [Month] varchar(255),
    [Category] varchar(255)
)

I have copied an example of the data below. There may be multiple items with the same category, and different status code count, and I use this in a pie chart with Values of status code count (Sum), and arguments of Category, Code and Description. This allows the pie to be drilled down, starting with category. (When you drill down on a category, you see all the codes/descriptions that make up that category.)

So what I would like to do, without changing my main proc, which is quite temperamental, is change the final select statement that selects the aggregated data. I want to exclude anything where the sum of the status code count, for all rows with the same category, if the percentage of sum status code count for that category, of the total count, will yield a percentage that will round to zero when cast to int.

The select statement at the end of the proc looks like this:

SELECT 
    Code, Description, SUM([Status Code Count]) AS [Status Code Count], 
    [Month], Category 
FROM @Responses 
GROUP BY code, [Description], [Month], Category

The data looks like this:

+------+---------------------------------------------------------------------------------------------+-------------------+---------------+--------------------------+
| Code |                                         Description                                         | Status Code Count |     Month     |         Category         |
+------+---------------------------------------------------------------------------------------------+-------------------+---------------+--------------------------+
|   10 | Account in Sequestration                                                                    |                 1 | October, 2014 | Other                    |
|   12 | ACCOUNT CLOSED                                                                              |              1788 | October, 2014 | Other                    |
|   18 | Account Holder Deceased                                                                     |                54 | October, 2014 | Other                    |
|    2 | Not Provided For                                                                            |             59556 | October, 2014 | 2 Not Provided For       |
|   22 | Account effects not cleared                                                                 |                 1 | October, 2014 | Other                    |
|   26 | NO SUCH ACCOUNT                                                                             |              1176 | October, 2014 | Other                    |
|    3 | DEBITS / CREDITS NOT ALLOWED TO ACCOUNT                                                     |                61 | October, 2014 | Other                    |
|    3 | Debits not allowed for this account                                                         |               134 | October, 2014 | Other                    |
|   30 | CLIENT DID NOT AUTHORISE DEBIT                                                              |               697 | October, 2014 | Other                    |
|   30 | No authority to debit                                                                       |              2097 | October, 2014 | 30 No authority to debit |
|   32 | DEBIT CONTRAVENES CLIENT'S AUTHORITY                                                        |                39 | October, 2014 | Other                    |
|   32 | Debit in contravention of payer's authority                                                 |               238 | October, 2014 | Other                    |
|   32 | Upfront Reply *** WARNING ONLY *** HOMING BRANCH INVALID - TRANSACTION REJECTED             |                47 | October, 2014 | Upfront Rejection        |
|   34 | AUTHORISATION CANCELLED                                                                     |               203 | October, 2014 | Other                    |
|   34 | Upfront Reply *** WARNING ONLY ** NAEDO TXN NOT ALLOWED-TXN REJ                             |               345 | October, 2014 | Upfront Rejection        |
|   34 | Upfront Reply *** WARNING ONLY *** HOMING ACCOUNT INVALID - TRANSACTION REJECTED            |               283 | October, 2014 | Upfront Rejection        |
|   34 | Upfront Reply *** WARNING ONLY *** NAEDO TRANSACTION NOT ALLOWED - TRANSACTION REJECTED *** |                 4 | October, 2014 | Upfront Rejection        |
|   35 | Upfront Reply *** WARNING ONLY ** ACCOUNT TYPE INVALID-TXN REJ                              |                 2 | October, 2014 | Upfront Rejection        |
|   36 | PREVIOUSLY STOPPED AS STOP PAYMENT                                                          |                11 | October, 2014 | Other                    |
|   36 | Previously stopped via stop payment advice                                                  |                86 | October, 2014 | Other                    |
|    4 | PAYMENT STOPPED                                                                             |               865 | October, 2014 | Other                    |
|    4 | Payment Stopped by Account Holder                                                           |               213 | October, 2014 | Other                    |
|    5 | Account Dormant                                                                             |                70 | October, 2014 | Other                    |
|   56 | Not FICA Compliant                                                                          |                 4 | October, 2014 | Other                    |
|    6 | ACCOUNT FROZEN                                                                              |               655 | October, 2014 | Other                    |
|  899 | DISTRIBUTION UPFRONT REJECTION                                                              |                18 | October, 2014 | Other                    |
|  908 | EXCEPTIONS ERROR                                                                            |                 1 | October, 2014 | Other                    |
+------+---------------------------------------------------------------------------------------------+-------------------+---------------+--------------------------+

Upvotes: 0

Views: 397

Answers (2)

CrimsonKing
CrimsonKing

Reputation: 2886

SELECT 
    Code, Description, SUM([Status Code Count]) AS [Status Code Count], 
    [Month], a.Category 
FROM @Responses a
JOIN (SELECT Category, SUM([Status Code Count]) AS Total FROM @Responses GROUP BY Category) b ON a.Category = b.Category
GROUP BY code, [Description], [Month], a.Category
HAVING CONVERT(INT, 100 * SUM([Status Code Count]) / Total) >= 1

Upvotes: 2

Jerome Viveiros
Jerome Viveiros

Reputation: 241

As much as I'd like to accept CrimsonKing's answer, it's not giving me the result I expect. (I will try to modify it to make it work though, and then accept it if I can. It's much simpler than this...)

Here's my solution that works but is ugly.

Change the table definition to this:

DECLARE @Responses TABLE
(
    Code varchar(10),
    Description varchar(255),
    [Status Code Count] int,
    [Month] varchar(255),
    [Category] varchar(255),
    Percentage float
)

Then instead of a select at the end, use a cursor like so:

DECLARE @Count float
SELECT @Count = SUM([Status Code Count]) from @Responses
DECLARE @Category varchar(255) 
DECLARE totals_cursor CURSOR FOR 
SELECT Category FROM @Responses

OPEN totals_cursor

FETCH NEXT FROM totals_cursor INTO @Category

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @Percentage float 
    SELECT @Percentage=SUM([Status Code Count])/@Count*100 FROM @Responses WHERE Category = @Category
    UPDATE @Responses SET Percentage = @Percentage WHERE Category = @Category
    FETCH NEXT FROM totals_cursor INTO @Category
END

CLOSE totals_cursor;
DEALLOCATE totals_cursor;

SELECT Code, Description, SUM([Status Code Count]) AS [Status Code Count], [Month], Category
FROM @Responses
WHERE CAST(percentage as int) > 0
GROUP BY code, [Description], [Month], Category

Upvotes: 0

Related Questions