CODEWITHSUNDEEP
sqlsas
Sassafras
Sassafras

Reputation: 301

Computing percentages within subtotals

I am trying to compute percentages of a subtotal in one sql statement. This requires using a subquery, but thus far not able to adapt SAS's example.

Requires 2 steps:

  1. Compute subtotal of TotalCargo by LoadArea.
  2. Sum the subtotals and divide each LoadArea's subtotal by sum.

Divide subtotal by sum of all the subtotals.

 ShipmentID   LoadArea    TotalCargo
 1            BRZ         200000
 2            BRZ         250000
 3            AG          200000
 4            AG          250000 



 End result:
 LoadArea  TotalCargo  Vol_share
 AG        450000      .5
 BRZ       450000      .5

Upvotes: 2

Views: 1949

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Like so:

SELECT 
  LoadArea, 
  SUM(TotalCargo)AS TotalCargo,
  (SUM(TotalCargo)* 1.0) / 
  (SELECT SUM(TotalCargo) FROM Table1) AS Vol_share
FROM Table1 
GROUP BY LoadArea;

SQL Fiddle Demo

This will give you:

| LOADAREA | TOTALCARGO | VOL_SHARE |
-------------------------------------
|       AG |     450000 |       0.5 |
|      BRZ |     450000 |       0.5 |

Upvotes: 1

Related Questions