Using temporary data in SQL query

Question

I have a table (maintable) of the format:

(id, intcol1, intcol2, intcol3, ...)

Sample data:

123, 582585, 25215718, 15519
234, 2583, 2371, 1841948
345, 42389, 234289, 234242

I want to run some aggregate calculations using outside data to group the data by. The data I have is of the form:

(id, groupcol)

Sample data:

123, "January",
234, "February"
345, "January"

In this case, suppose I want the SUM of intcol1 using the provided groupings to match IDs, the result would be:

"January", 624974               # 42389 + 582585
"February", 2583

My question is: How do I get the "group data" into the query? Using a WITH clause and JOINING it against the maintable? Or adding it to a temporary table and using that in a following query?

I can manipulate the data I have and format it however way is easiest from the program running the SQL query.

What is the best / fastest / simplest method?

Edited for clarity

@a_horse_with_no_name Done!

Christian P.
– Christian P.

2017-07-11 11:24:47 +00:00
Commented Jul 11, 2017 at 11:24 — Christian P.
– Christian P., Commented Jul 11, 2017 at 11:24
Where does the 624974 in 624974 + 582585 come from?

user330315
– user330315

2017-07-11 11:30:30 +00:00
Commented Jul 11, 2017 at 11:30 — user330315
– user330315, Commented Jul 11, 2017 at 11:30
@a_horse_with_no_name Me working too fast >_< Fixed now

Christian P.
– Christian P.

2017-07-11 12:55:32 +00:00
Commented Jul 11, 2017 at 12:55 — Christian P.
– Christian P., Commented Jul 11, 2017 at 12:55

user330315 · Accepted Answer · 2017-07-11 13:40:31Z

2

That looks like a simple join and group by:

select t2.groupcol, 
       sum(t1.intcol1)
from maintable t1 
  join table_2 t2 on t1.id = t2.id
group by t2.groupcol;

If you don't have that "outside" data in a table, you can do something like this:

select t2.groupcol, 
       sum(t1.intcol1)
from maintable t1 
  join (
    values 
      (123, 'January'), 
      (234, 'February'),
      (345, 'January')
  ) as t2 (id, groupcol) on t1.id = t2.id
group by t2.groupcol;

answered Jul 11, 2017 at 13:40

user330315

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

Collectives™ on Stack Overflow

Using temporary data in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related