CODEWITHSUNDEEP
sqlsql-server
ThankfulChap
ThankfulChap

Reputation: 11

Pivot and Sum of values in column based on specific row condition

I have a sample table like this:

DaysInOp  Zone_id   DailyAvg                RunNumber
    0       9       5996.79891226986        1
    0       15      0                       1
    0       3       12549.5152623033        1
    0       12      -8400.75508952542       1
    0       6       24766.7738319152        1
    0       7       11737.3698582701        1
    0       1       26395.0374347879        1
    0       18      0.0218623184855433      1
    0       10      1965.53524850589        1
    0       4       9499.54051554152        1
    0       13      6158.45142253444        1

Where DaysinOp could be from 0-300 and Zone ID=1-20 and DailyAvg= Variable and RunNumber =1-5

I would like output like:

Day #   Run 1 Weight (lbs)  Run 2 Weight (lbs)  Run 3 Weight (lbs)  Run 4 Weight (lbs)
0             15                      5            55                   1
1             17                     16           612                   34
2             19                     55          1100                  56
3            22                       100            1230              456
Etc.    …   …   …   …

Where Each run weight is Sum of daily avg values of Zone ID (1-10).

Please let me know if this is possible.

I am trying pivot with sum condition? Any hints will be appreciated.

Upvotes: 0

Views: 59

Answers (2)

Sentinel
Sentinel

Reputation: 6449

@MatBailie provided the manual pivot, here's the same query using the pivot operator:

with t1 as (
  select DaysInOp
       , case when zone_id between 1 and 10 then dailyavg end rw
       , RunNumber
    from table1
)
select DaysInOp [Day #]
     , [1] [Run 1 Weight (lbs)]
     , [2] [Run 2 Weight (lbs)]
     , [3] [Run 3 Weight (lbs)]
     , [4] [Run 4 Weight (lbs)]
     , [5] [Run 5 Weight (lbs)]
  from t1
  pivot (sum(rw)
    for RunNumber in ([1],[2],[3],[4],[5])) pvt

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

SELECT
  DaysInOp,
  SUM(CASE WHEN Zone_id BETWEEN 1 AND 10
            AND RunNumber = 1            THEN DailyAvg END)   AS Run1Weight,
  SUM(CASE WHEN Zone_id BETWEEN 1 AND 10
            AND RunNumber = 2            THEN DailyAvg END)   AS Run2Weight,
  SUM(CASE WHEN Zone_id BETWEEN 1 AND 10
            AND RunNumber = 3            THEN DailyAvg END)   AS Run3Weight,
  SUM(CASE WHEN Zone_id BETWEEN 1 AND 10
            AND RunNumber = 4            THEN DailyAvg END)   AS Run4Weight,
  SUM(CASE WHEN Zone_id BETWEEN 1 AND 10
            AND RunNumber = 5            THEN DailyAvg END)   AS Run5Weight
FROM
  yourTable
GROUP BY
  DaysInOp

Can be simplified by moving the Zone_id BETWEEN 1 AND 10 to a WHERE clause. If a day has no records in those zones, however, it won't appear in the results.

SELECT
  DaysInOp,
  SUM(CASE WHEN RunNumber = 1 THEN DailyAvg END)   AS Run1Weight,
  SUM(CASE WHEN RunNumber = 2 THEN DailyAvg END)   AS Run2Weight,
  SUM(CASE WHEN RunNumber = 3 THEN DailyAvg END)   AS Run3Weight,
  SUM(CASE WHEN RunNumber = 4 THEN DailyAvg END)   AS Run4Weight,
  SUM(CASE WHEN RunNumber = 5 THEN DailyAvg END)   AS Run5Weight
FROM
  yourTable
WHERE
  Zone_id BETWEEN 1 AND 10
GROUP BY
  DaysInOp

Upvotes: 1

Related Questions