SQL Create view or procedure in a SUM query without adding new column?

Question

Supposed I have some sample data in table_name_a as below:

    code    val    remark   date
   -----------------------------------
1   00001    500    111      20191108
2   00001   1000    222      20191107
3   00002    200    111      20191108
4   00002    400    222      20191108
5   00001    200    333      20191108
6   00001    400    444      20191108

And I have a SQL query as below:

SELECT t.code, 
       sum(CASE WHEN t.remark IN ('111', '222', '333')
       THEN t.value ELSE 0 END) AS sum_123
FROM table_name_a t
    WHERE t.code='00001' and t.date='20191108'
GROUP BY t.code

Output as below:

    code    sum_123
   ------------------
1   00001    1100

Now I would like to create a view or procedure use above SQL query(Oracle), we don't want a new column, is it possible to let sum_123 store into remark column? Supposed if I use below SQL query

SELECT t.code, t.value
FROM table_name_a t 
    WHERE t.remark='sum_123' and t.code='00001' and t.date='20191108'
GROUP BY t.code

and if I use above SQL query, my expected output as below:

    code    val        remark          date
   ------------------------------------------
1   00001    1100    sum_123          20191108

Is it possible to use create view/procedure save remark and val with sum_123and the value/result of 1100? Thanks so much for any advice.

@GordonLinoff Yes, I left the `group by` out, thanks for reminding — , Nov 20 '19 at 02:44
@Tejash I want that sum query to save into `remark`, then I can only use the simple query — , Nov 20 '19 at 02:46
So you also want something like sum_456 in your view? Or you want only sum_123 for different dates? — Popeye, Nov 20 '19 at 02:47
@Tejash Hi, yes, I also need `sum_456`, and also need to let one remark '111' save into such as `only_1`, `only_2` — , Nov 20 '19 at 02:52
You must edit your question and include expected output in detail. — Popeye, Nov 20 '19 at 03:00
@Tejash My question indeed included expected outputI am sorry, it was unclear, already edited again. 1 00001 1100 sum_123 20191108 , — , Nov 20 '19 at 03:08
You need to mention that you need not only one record but also record for 456 and also only_1 etc to make it clear, so that experts can help you. — Popeye, Nov 20 '19 at 03:16

Gordon Linoff · Accepted Answer · 2019-11-20T02:41:57.760

0

Is this what you want?

select t.code, 
       sum(case when t.remark IN ('111', '222', '333') then t.value else 0 end) as val,
       'sum_123' as remark,
       date
from table_name_a t
where t.code = '00001' and t.date = '20191108'
group by t.code, date;

edited Nov 20 '19 at 02:41

answered Nov 20 '19 at 02:39

Gordon Linoff

1,242,037
58
646
786

@Tejash . . . Not in Oracle. Good catch. – Gordon Linoff Nov 20 '19 at 02:42
@Tejash I left the `group by` out, already updated, thanks for reminding – Nov 20 '19 at 02:47
Just use `'sum_123' as remark`, then the `val` will be saved into `remark` with `sum_123`? I will try, thanks so much for the answer – Nov 20 '19 at 02:49
@nullfearless . . . The names of the columns are just the aliases you assign to the expression. – Gordon Linoff Nov 20 '19 at 02:53
Hi, `remark` The column of the database's table `table_name_a`, I am not sure I can use `create view` with this query, I will try, thanks again for help – Nov 20 '19 at 02:56
Hey, I also need `sum_456`, and also need to let one remark '111' save into such as only_1, only_2, can I same code? – Nov 20 '19 at 05:34
@nullfearless . . . If you cannot figure out how to modify this query, then ask a new question. And yes, the query is compatible with a view. – Gordon Linoff Nov 20 '19 at 12:56

SQL Create view or procedure in a SUM query without adding new column?

1 Answers1