CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

SUM based on different Types

I have the following query:

 SELECT 
    ISNULL(DrugName,'Sub Total:') as Drug, 
    SUM(COUNT) as Count, 
    Percentage
 FROM 
 (
    SELECT 
        DrugName, 
        DrugCategoryName,
         COUNT(*) as Count,
         CONVERT(DECIMAL(10,2),COUNT(*) * 100.0 / SUM(COUNT(*)) over ()) as Percentage
      FROM 
        Visit V 
    INNER JOIN Drug D on
        V.DrugID = D.DrugID 
    INNER JOIN DrugCategory DC on 
        D.DrugCategoryID = DC.DrugCategoryID
     GROUP BY GROUPING SETS
        ((DrugName, DrugCategoryName))
 ) a
 GROUP BY GROUPING SETS 
    ((DrugName, DrugCategoryName, Percentage), (DrugCategoryName))

which gives the following results:

 Drug                            Count  Percentage
 Amphetamines                    401    4.24
 Benzodiazapine                  435    4.60
 Biodone                         459    4.85
 Sub Total:                      1295   NULL
 Brown Heroin                    436    4.61
 Buprenorphine                   396    4.18
 Cocaine                         444    4.69
 Did not inject                  404    4.27
 Endone                          450    4.75
 Fentanyl Patch                  404    4.27
 Heroin                          1365   14.42
 Heroin & Cocaine                448    4.73
 Ice/Crystal/Meth                889    9.39
 Sub Total:                      5236   NULL
 Kapanol                         427    4.51
 Methadone                       430    4.54
 Methadone Syrup                 394    4.16
 Morphine                        417    4.41
 MS Contin                       438    4.63
 MS Mono                         424    4.48
 Other Amphetamines              404    4.27
 Sub Total:                      2934   NULL

What I'm needing to do also is calculate the Sub Total Percentages, so for example the first Sub Total is 1295 so that would calculate a percentage based off 1295 + 5236 + 2934 and get a value of 13.68%, and so on for the other Sub Total rows.

How could this be done dynamically so no matter the amount of Sub Totals it will calculate it correctly?

Upvotes: 2

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you can just calculate the total in the subquery and divide:

SELECT COALESCE(DrugName, 'Sub Total:') as Drug, 
       SUM(COUNT) as Count, 
       SUM(COUNT) / total_cnt as Percentage
FROM (SELECT DrugName, DrugCategoryName,
             COUNT(*) as Count,
             CONVERT(DECIMAL(10,2), COUNT(*)) * 100.0 / SUM(COUNT(*)) over () as Percentage,
             SUM(1.0*COUNT(*)) OVER () as total_cnt
      FROM Visit V INNER JOIN  
           Drug D 
           ON V.DrugID = D.DrugID INNER JOIN
           DrugCategory DC 
           ON D.DrugCategoryID = DC.DrugCategoryID
      GROUP BY DrugName, DrugCategoryName
     ) vd
GROUP BY GROUPING SETS ((DrugName, DrugCategoryName, Percentage, Total_cnt),
                        (DrugCategoryName, Total_Cnt))

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

 WITH CTE AS 
(
 SELECT 
    ISNULL(DrugName,'Sub Total:') as Drug, 
    SUM(COUNT) as Count, 
    Percentage
 FROM 
 (
    SELECT 
        DrugName, 
        DrugCategoryName,
         COUNT(*) as Count,
         CONVERT(DECIMAL(10,2),COUNT(*) * 100.0 / SUM(COUNT(*)) over ()) as Percentage
      FROM 
        Visit V 
    INNER JOIN Drug D on
        V.DrugID = D.DrugID 
    INNER JOIN DrugCategory DC on 
        D.DrugCategoryID = DC.DrugCategoryID
     GROUP BY GROUPING SETS
        ((DrugName, DrugCategoryName))
 ) a
 GROUP BY GROUPING SETS 
    ((DrugName, DrugCategoryName, Percentage), (DrugCategoryName)))

      SELECT [DRUG],[COUNT] ,ISNULL([PERCENTAGE],([COUNT]/S)*100) AS [PERCENTAGE] FROM CTE A CROSS APPLY
     (SELECT CAST(SUM([COUNT])AS NUMERIC(22,6)) AS S  FROM CTE B WHERE DRUG='SUB TOTAL:')C

OUTPUT

DRUG    COUNT   PERCENTAGE
Amphetamines    401 4.240000
Benzodiazapine  435 4.600000
Biodone 459 4.850000
Sub Total:  1295    13.681986
Brown Heroin    436 4.610000
Buprenorphine   396 4.180000
Cocaine 444 4.690000
Did not inject  404 4.270000
Endone  450 4.750000
Fentanyl Patch  404 4.270000
Heroin  1365    14.420000
Heroin & Cocaine    448 4.730000
Ice/Crystal/Meth    889 9.390000
Sub Total:  5236    55.319599
Kapanol 427 4.510000
Methadone   430 4.540000
Methadone Syrup 394 4.160000
Morphine    417 4.410000
MS Contin   438 4.630000
MS Mono 424 4.480000
Other Amphetamines  404 4.270000
Sub Total:  2934    30.998415

Upvotes: 1

Related Questions