CODEWITHSUNDEEP
sqloracle-databasegroup-bysum
Sawan S
Sawan S

Reputation: 97

Adding rows with missing entries

I have a table in the below format with multiple Id's, months (1-72) multiple measures (1-40). Below is the sample table. The entries for a few months are missing for each measure as seen (Month 5,8 is missing for Measure1, month 8 and 9 is missing for Measure3 and so on). I want to create a new measure, say measure 4 which is the sum of 2 measures (say measure1+measure3) for each Id, month. I tried using the lag/lead functions but I could not account it for missing months.

+----+-------+--------------+---------------+
| ID | month | measure name | measure value |
+----+-------+--------------+---------------+
|  1 |     1 | Measure1     |            20 |
|  1 |     2 | Measure1     |            80 |
|  1 |     3 | Measure1     |            60 |
|  1 |     4 | Measure1     |            40 |
|  1 |     6 | Measure1     |           100 |
|  1 |     7 | Measure1     |           250 |
|  1 |     9 | Measure1     |            60 |
|  1 |    10 | Measure1     |            70 |
|  1 |     1 | Measure2     |            40 |
|  1 |     2 | Measure2     |            30 |
|  1 |     3 | Measure2     |            25 |
|  1 |     4 | Measure2     |            75 |
|  1 |     5 | Measure2     |           115 |
|  1 |     6 | Measure2     |            80 |
|  1 |     7 | Measure2     |            85 |
|  1 |     9 | Measure2     |            95 |
|  1 |    10 | Measure2     |           200 |
|  1 |     1 | Measure3     |           220 |
|  1 |     2 | Measure3     |            50 |
|  1 |     3 | Measure3     |            60 |
|  1 |     4 | Measure3     |            90 |
|  1 |     5 | Measure3     |            20 |
|  1 |     6 | Measure3     |            70 |
|  1 |     7 | Measure3     |            80 |
|  1 |    10 | Measure3     |           105 |
+----+-------+--------------+---------------+

The final output should look something like this (measure4 = measure1+measure3).

+----+-------+--------------+---------------+
| ID | month | measure name | measure value |
+----+-------+--------------+---------------+
|  1 |     1 | Measure4     |           240 |
|  1 |     2 | Measure4     |           130 |
|  1 |     3 | Measure4     |           120 |
|  1 |     4 | Measure4     |           130 |
|  1 |     5 | Measure4     |            20 |
|  1 |     6 | Measure4     |           170 |
|  1 |     7 | Measure4     |           330 |
|  1 |     9 | Measure4     |            60 |
|  1 |    10 | Measure4     |           175 |
+----+-------+--------------+---------------+

Can you please help me with this? Thanks

Upvotes: 0

Views: 33

Answers (1)

GMB
GMB

Reputation: 222402

You could filter on the two measures that you are interested in, then use aggregation:

select 
    month,
    'Measure4' measure_name,
    sum(measure_value) measure_value
from mytable 
where measure_name in ('Measure1', 'Measure3')
group by month 
order by month

This assumes that at least one of the two measures is available for each month (otherwise you would have missing monthes in the resultset), which is consistent with your sample data.

Upvotes: 1

Related Questions