CODEWITHSUNDEEP
sqloracle-databasebi-publisher
SHSH_123
SHSH_123

Reputation: 59

SQL Query to get the sum of two columns rest being the same value

I have created a query to get the following output-

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

using the query -

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

I want the sum of same elements for the column currentamount and hours_value. It is working mostly except for a few cases, where the hours are different for the same element. In the above case it being Regular Earnings Results. For this element, the output should have showed the sum of 244.62+1956.96 and hours_value = 54 but Rate =40.77 and other columns being just the same.

Expected output-

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

How is this possible ?

Upvotes: 0

Views: 1357

Answers (3)

Popeye
Popeye

Reputation: 35920

Your current query do not reveal the group by clause that you are using.

You need a proper group by clause and aggregate functions as follows:

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

It is recommended to use the standard ANSI joins.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

A GROUP BY should resolve your issue. Only issue is for your RATE value. It depends what you wants in your output. If all Rows contain the same value for a GROUP, You can simply calculate AVG or MIN or MAX and all these will return you the same result. But, if there are different value, you have to define your method what output you wants.

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

Upvotes: 0

Raj
Raj

Reputation: 274

Looks like you may need group by Element, SUM(currentamount), SUM(HOURS) from your table. you can select other required columns as well as per your requirement.

sample

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

Upvotes: 1

Related Questions