Adding column to another table in SQL

Question

I have a table of three numbers that are the percent difference of other numbers.

The query I've used looks like this:

select abs((((select avg(Number) from Table1 where File='File1') - 
    (select avg(Number) from Table1 where File='File2')) / 
    (select avg(Number) from Table1 where File='File2'))*100)
union all
select abs((((select avg(Number) from Table1 where File='File3') - 
    (select avg(Number) from Table1 where File='File4')) / 
    (select avg(Number) from Table1 where File='File4'))*100)
union all
select abs((((select avg(Number) from Table1 where File='File5') - 
    (select avg(Number) from Table1 where File='File6')) / 
    (select avg(Number) from Table1 where File='File6'))*100)

which results in a table like this:

| (No column name) |
|:----------------:|
|             1.54 |
|             1.15 |
|             2.04 |

These numbers are the percent difference for a category of data from three files. These file names are Column1 in Table1. How could I write this query so that the table looks like this instead? (Add the file names in a column to the left of these numbers).

| FileName | (No column name) |
|:--------:|:----------------:|
| File1    |             1.54 |
| File2    |             1.15 |
| File3    |             2.04 |

add column 1 to each query and group by column1?

S3S
– S3S

2017-05-12 19:09:10 +00:00
Commented May 12, 2017 at 19:09 — S3S
– S3S, Commented May 12, 2017 at 19:09

BWS · Accepted Answer · 2017-05-12 19:11:36Z

5

Simply add the file name to the query:

select FileName='File1', PCT_DIFF=abs((((select avg(Number) from Table1 where File='File1') - 
    (select avg(Number) from Table1 where File='File2')) / 
    (select avg(Number) from Table1 where File='File2'))*100)
union all
select 'File3', abs((((select avg(Number) from Table1 where File='File3') - 
    (select avg(Number) from Table1 where File='File4')) / 
    (select avg(Number) from Table1 where File='File4'))*100)
union all
select 'File5', abs((((select avg(Number) from Table1 where File='File5') - 
    (select avg(Number) from Table1 where File='File6')) / 
    (select avg(Number) from Table1 where File='File6'))*100)

answered May 12, 2017 at 19:11

BWS

3,85620 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neal Over a year ago

I'm a dolt. I've done this before. I don't know why I didn't think of this. Thank you for unclogging my brain.

Gordon Linoff · Accepted Answer · 2017-05-12 19:18:17Z

2

Simplify the query!

select file,
       100 * (prev_avg - avg) / avg as percent_diff
from (select file, avg(Number) as avg,
             lag(avg(Number)) over (order by file) as prev_avg
      from table1
      where file in ('File1', 'File2', 'File3', 'File4', 'File5', 'File6')
      group by file
     ) t
where file in ('File2', 'File4', 'File6');

This assumes that the files are ordered by name. If should be easy to modify if they are ordered in some other way -- say by date or size.

This also readily generalizes to additional rows.

answered May 12, 2017 at 19:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Adding column to another table in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related