sql查询以获取两列的总和，其余列的值相同

baubqpgj 于 2021-08-01 发布在 Java

关注(0)|答案(3)|浏览(412)

我创建了一个查询来获得以下输出-

Effective_date  Employee_number Name    CLASSIFICATIONNAME      Element                 CURRENTAMOUNT   HOURS   RATE    HOURS_VALUE COUNT_VALUE
6/5/2020        23              Jai         Standard Earnings   Benefit Credit  Results     6.75                1
6/5/2020        23              Jai         Standard Earnings   Regular Earnings Results    244.62          6   40.77      6            1
6/5/2020        23              Jai         Standard Earnings   Regular Earnings Results    1956.96         8   40.77      48           6
6/5/2020        23              Jai         Standard Earnings   Stat Holiday  Results       326.16          8   40.77      8            1
6/5/2020        23              Jai         Standard Earnings   Paid Time Off Results       326.16          8   40.77      8            1

使用查询-

select effective_date,
person_number Employee_number,
Name,
CLASSIFICATIONNAME,
Element,
SUM(resultvalue)  CURRENTAMOUNT,
HOURS,
RATE,
 (Count(resultvalue)) COUNT_VALUE,
(Count( resultvalue) * hours) HOURS_VALUE

from 

per_payroll_table pay,
per_all_people people
where pay.person_id  = people.person_id

我想要列currentamount和hours\u值的相同元素之和。它主要是工作除了少数情况下，其中的时间是不同的相同的元素。在上述情况下，这是正常收益的结果。对于这个元素，输出应该显示244.62+1956.96和hours_值=54之和，但rate=40.77，其他列则完全相同。
预期产量-

Effective_date  Employee_number Name    CLASSIFICATIONNAME      Element                 CURRENTAMOUNT   HOURS   RATE    HOURS_VALUE COUNT_VALUE
6/5/2020        23              Jai         Standard Earnings   Benefit Credit  Results     6.75                1

* 6/5/2020       23              Jai         Standard Earnings   Regular Earnings Results    2201.58     14      40.77      54           7*

6/5/2020        23              Jai         Standard Earnings   Stat Holiday  Results       326.16      8       40.77      8            1
6/5/2020        23              Jai         Standard Earnings   Paid Time Off Results       326.16      8       40.77      8            1

这怎么可能？

sql oracle bi-publisher

来源：https://stackoverflow.com/questions/62650665/sql-query-to-get-the-sum-of-two-columns-rest-being-the-same-value

3条答案

按热度按时间

lqfhib0f1#

一个小组应该解决你的问题。唯一的问题是你的价格价值。这取决于你想要的是什么。如果一个组的所有行都包含相同的值，您可以简单地计算avg、min或max，所有这些都将返回相同的结果。但是，如果有不同的值，则必须定义方法所需的输出。

SELECTeffective_date,
person_number Employee_number,
Name,
CLASSIFICATIONNAME,
Element,
SUM(resultvalue)  CURRENTAMOUNT,
SUM(HOURS),
AVG(RATE), -- Avg should keep the same value if rate is same for the group
(Count(resultvalue)) COUNT_VALUE,
(Count( resultvalue) * hours) HOURS_VALUE
from 
-- Use standard joining
per_payroll_table pay 
INNER JOIN per_all_people people
ON pay.person_id  = people.person_id
GROUP BY ffective_date,
person_number Employee_number,
Name,
CLASSIFICATIONNAME,
Element

赞(0）回复(0）举报 2021-08-01

ljo96ir52#

看起来你可能需要分组 Element , SUM(currentamount) , SUM(HOURS) 从你的table上。您可以根据需要选择其他所需列。
样品

select Element, SUM(currentamount), SUM(HOURS)
from <your_table>
group by Element

赞(0）回复(0）举报 2021-08-01

i5desfxk3#

您当前的查询没有显示 group by 您正在使用的子句。
你需要一个合适的 group by 条款和 aggregate functions 具体如下：

SELECT 
  effective_date,
  person_number Employee_number,
  Name,
  CLASSIFICATIONNAME,
  Element,
  SUM(resultvalue) CURRENTAMOUNT,
  SUM(HOURS) as HOURS, -- use sum aggregate function here
  RATE, 
  Count(resultvalue) COUNT_VALUE,
  Count(resultvalue) * SUM(HOURS) HOURS_VALUE -- use sum aggregate function on hours here
from 
  per_payroll_table pay 
  INNER JOIN per_all_people people
  ON pay.person_id  = people.person_id
GROUP BY 
  effective_date,
  person_number,
  Name,
  CLASSIFICATIONNAME,
  Element,
  Rate

建议使用标准ansi联接。

赞(0）回复(0）举报 2021-08-01

我来回答

sql查询以获取两列的总和，其余列的值相同

3条答案

相关问题

热门标签

最新问答