英文:
Oracle SQL query to get the sum of elements for an employee
问题
I have translated the content you provided:
我有一张表格，其中包含与相应输入值对应的元素的多个值：
EE ELEMENT_NAME RESULT_VALUE EFFECTIVE DATE INPUT VALUE
10 REGULAR RETRO 3457 10-APR-2023 earning
10 REGULAR RETTO -3454 10-APR-2023 earning
10 REGULAR SALAR 34000 10-APR-2023 Pay
11 REGULAR SALAR 670000 10-APR-2023 earning
11 DENTAL SALAR 100 10-APR-2023 Pay
11 HEALTH SALAR 2000 10-APR-2023 Pay
11 Car SALAR 2000 10-APR-2023 Pay
12 EE RETRO -3000 10-APR-2023 Pay
12 REGULAR RETRO 1000 10-APR-2023 earning
12 REGULAR RETRO -890 10-APR-2023 earning
12 Tax RETRO -890 10-APR-2023 earning
所需的输出是：
EE REGULAR_RETRO REGULAR_SALAR DENTAL_SALAR HEALTH_SALAR EE_RETRO
10 3 34000
11 670000 100 2000
12 110 -3000
以下查询仅适用于我在查询的“where子句”中定义的特定元素，但尽管我使用了group by，但仍然为同一名员工获取多行。而且输出还没有汇总回退值（常规回退），而是提供了重复的行。
我使用的查询是：
select ee person_number,
case when peen.element_name in 'REGULAR RETRO'
then sum (RESULT_VALUE) REGULAR_RETRO,
case when peen.element_name in 'REGULAR SALAR'
then sum (RESULT_VALUE) REGULAR_SALAR,
case when peen.element_name in 'DENTAL SALAR'
then sum (RESULT_VALUE) DENTAL_SALAR,
case when peen.element_name in 'HEALTH SALAR'
then sum (RESULT_VALUE) HEALTH_SALAR,
case when peen.element_name in 'EE RETRO'
then sum (RESULT_VALUE) EE_RETRO
FROM
PER_ALL_PEOPLE_F PAPF
PER_ELEMENT_NAME PEEN,
PEr_ELEMENT_TYPE PET
WHERE
PAPF.PERSON_ID = PEEN.PERSON_ID
AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
AND PET.INPUT_VALUE IN ('Pay','Earning')
and peen.element_name in
(
'REGULAR SALAR',
'DENTAL SALAR',
'HEALTH SALAR',
'EE RETRO',
'REGULAR RETRO'
)
group by person_number,
peen.element_name
Please note that this translation is based on the provided text, and the code contains some HTML entities (e.g., ") that may need to be adjusted depending on the actual programming environment you are using.
英文:
I have a table with multiple values of elements for an element with corresponding input values:
EE ELEMENT_NAME RESULT_VALUE EFFECTIVE DATE INPUT VALUE
10 REGULAR RETRO 3457 10-APR-2023 earning
10 REGULAR RETTO -3454 10-APR-2023 earning
10 REGULAR SALAR 34000 10-APR-2023 Pay
11 REGULAR SALAR 670000 10-APR-2023 earning
11 DENTAL SALAR 100 10-APR-2023 Pay
11 HEALTH SALAR 2000 10-APR-2023 Pay
11 Car SALAR 2000 10-APR-2023 Pay
12 EE RETRO -3000 10-APR-2023 Pay
12 REGULAR RETRO 1000 10-APR-2023 earning
12 REGULAR RETRO -890 10-APR-2023 earning
12 Tax RETRO -890 10-APR-2023 earning
The required output is:
EE REGULAR_RETRO REGULAR_SALAR DENTAL_SALAR HEALTH_SALAR EE_RETRO
10 3 34000
11 670000 100 2000
12 110 -3000
The below query I am using is only for specific elements that I am defining in "where clause" of my query, but I am getting multiple rows for the same employee although I am using group by.
And the output is also not summing up the retros values (regular retros) and is giving repeated rows instead.
The query I am using is:
select ee person_number,
case when peen.element_name in 'REGULAR RETRO'
then sum (RESULT_VALUE) REGULAR_RETRO,
case when peen.element_name in 'REGULAR SALAR'
then sum (RESULT_VALUE) REGULAR_SALAR,
case when peen.element_name in 'DENTAL SALAR'
then sum (RESULT_VALUE) DENTAL_SALAR,
case when peen.element_name in 'HEALTH SALAR'
then sum (RESULT_VALUE) HEALTH_SALAR,
case when peen.element_name in 'EE RETRO'
then sum (RESULT_VALUE) EE_RETRO
FROM
PER_ALL_PEOPLE_F PAPF
PER_ELEMENT_NAME PEEN,
PEr_ELEMENT_TYPE PET
WHERE
PAPF.PERSON_ID = PEEN.PERSON_ID
AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
AND PET.INPUT_VALUE IN ('Pay','Earning')
and peen.element_name in
(
'REGULAR SALAR',
'DENTAL SALAR',
'HEALTH SALAR',
'EE RETRO',
'REGULAR RETRO'
)
group by person_number,
peen.element_name
答案1
得分: 2
这可以通过使用条件聚合和 group by 以及 sum() 来实现：
select EE,
sum(case when ELEMENT_NAME = 'REGULAR RETRO' then RESULT_VALUE else 0 end) as REGULAR_RETRO,
sum(case when ELEMENT_NAME = 'REGULAR SALAR' then RESULT_VALUE else 0 end) as REGULAR_SALAR,
sum(case when ELEMENT_NAME = 'DENTAL SALAR' then RESULT_VALUE else 0 end) as DENTAL_SALAR,
sum(case when ELEMENT_NAME = 'HEALTH SALAR' then RESULT_VALUE else 0 end) as HEALTH_SALAR,
sum(case when ELEMENT_NAME = 'EE RETRO' then RESULT_VALUE else 0 end) as EE_RETRO
from mytable
group by EE
结果：
EE |REGULAR_RETRO |REGULAR_SALAR |DENTAL_SALAR |HEALTH_SALAR |EE_RETRO
----|-----|----|---|---|----|---
10 |3 |34000 |0 |0 |0
11 |0 |670000 |100 |2000 |0
12 |110 |0 |0 |0 |-3000
英文:
This can be done using the conditional aggregation using group by and sum() :
select EE,
sum(case when ELEMENT_NAME = 'REGULAR RETRO' then RESULT_VALUE else 0 end) as REGULAR_RETRO,
sum(case when ELEMENT_NAME = 'REGULAR SALAR' then RESULT_VALUE else 0 end) as REGULAR_SALAR,
sum(case when ELEMENT_NAME = 'DENTAL SALAR' then RESULT_VALUE else 0 end) as DENTAL_SALAR,
sum(case when ELEMENT_NAME = 'HEALTH SALAR' then RESULT_VALUE else 0 end) as HEALTH_SALAR,
sum(case when ELEMENT_NAME = 'EE RETRO' then RESULT_VALUE else 0 end) as EE_RETRO
from mytable
group by EE
Result :
| EE | REGULAR_RETRO | REGULAR_SALAR | DENTAL_SALAR | HEALTH_SALAR | EE_RETRO | |
|---|---|---|---|---|---|---|
| 10 | 3 | 34000 | 0 | 0 | 0 | |
| 11 | 0 | 670000 | 100 | 2000 | 0 | |
| 12 | 110 | 0 | 0 | 0 | -3000 |
答案2
得分: 0
修复查询中的CASE语法并从“group by”中移除peen.element_name应该解决问题。
英文:
Fix the syntax of the CASE in your query and remove the peen.element_name from the "group by" should solve the problem.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论