Why SUM values are wrong in postgresql query?

Question

I use postgresql and I have trouble in forming the correct query to get the desired result.

I have a query

    select distinct tt.ticket,tt.submitter,tt.seconds_worked as hours from ticket_time tt join ticket_custom tc on tt.ticket = tc.ticket where tc.ticket in (select ticket from ticket_custom where name='opportunity_id' and value in ('ABCD020003')) group by tt.ticket,tt.submitter,tt.seconds_worked;

And it gives me result as below.

ticket |      submitter      | hours 
--------+---------------------+-------
    360 | elango.kumar        | 10800
    360 | elango.kumar        | 18000
    360 | elango.kumar        | 21600
    360 | elango.kumar        | 32400
    360 | gopenath.palanisamy |  7200
    360 | gopenath.palanisamy | 10800
    360 | gopenath.palanisamy | 14400
    360 | lea.nair            |  3600
    360 | lea.nair            | 10800
    360 | lea.nair            | 14400
    360 | lea.nair            | 18000
    360 | lea.nair            | 21600
    360 | lea.nair            | 25200
    360 | lea.nair            | 32400
    360 | muthupandi.selvaraj |  7200
    360 | muthupandi.selvaraj | 10800
    360 | muthupandi.selvaraj | 19800
    360 | prabodh.panda       | 10800
    360 | prabodh.panda       | 14400
    360 | prabodh.panda       | 16200
    361 | bhashyam.narasimhan |  5400
    361 | bhashyam.narasimhan |  7200
    361 | bhashyam.narasimhan | 19800
    361 | bhashyam.narasimhan | 30600
    361 | bhashyam.narasimhan | 32400
    361 | lea.nair            | 28800
    366 | sangeetha.sankar    |  7200
    366 | sangeetha.sankar    | 32400
    398 | pradeep.kirthivasan | 14400
    398 | pradeep.kirthivasan | 25200
(30 rows)

I wish to get the result like

360 | elango.kumar        | 82800
360 | gopenath.palanisamy | 32400

So I tried to SUM the third column and so changed the query a bit like this,

select distinct tt.ticket,tt.submitter,SUM(tt.seconds_worked) as hours from ticket_time tt join ticket_custom tc on tt.ticket = tc.ticket where tc.ticket in (select ticket from ticket_custom where name='opportunity_id' and value in ('ABCD020003')) group by tt.ticket,tt.submitter,tt.seconds_worked;

But the result is like below,

 ticket |      submitter      |   hours    
--------+---------------------+------------
    360 | gopenath.palanisamy |     158400
    361 | bhashyam.narasimhan |     217800
    361 | bhashyam.narasimhan |     712800
    361 | bhashyam.narasimhan |     336600
    360 | prabodh.panda       |     178200
    360 | lea.nair            |     158400
    360 | elango.kumar        | 2.1384e+06
    360 | prabodh.panda       |     118800
    366 | sangeetha.sankar    |     158400
    361 | lea.nair            |     316800
    360 | gopenath.palanisamy |     633600
    360 | lea.nair            |     475200
    360 | prabodh.panda       |     316800
    360 | lea.nair            |     198000
    360 | elango.kumar        |     198000
    360 | elango.kumar        |     118800
    398 | pradeep.kirthivasan |     277200
    360 | muthupandi.selvaraj |     217800
    398 | pradeep.kirthivasan |     158400
    366 | sangeetha.sankar    | 1.4256e+06
    360 | muthupandi.selvaraj |      79200
    361 | bhashyam.narasimhan |      59400
    360 | elango.kumar        |     237600
    360 | lea.nair            |     237600
    360 | lea.nair            |      79200
    360 | muthupandi.selvaraj |     118800
    360 | lea.nair            |     712800
    360 | lea.nair            |     831600
    361 | bhashyam.narasimhan |     158400
    360 | gopenath.palanisamy |     237600
(30 rows)

Fist this is not what I expected and the third column in above result has some values with + sign in it and also a float value!!!

I don't really understand what is wrong here. Please help me to understand this and give me idea how I can get the desired result as I stared above.

Thanks in Advance!

Sami Kuhmonen · Accepted Answer · 2020-10-11 14:56:15Z

1

Since you want to get the information based on ticket and submitter then you need to group only by those columns. So something like

select tt.ticket,tt.submitter,sum(tt.seconds_worked) as hours
    from ticket_time tt
    join ticket_custom tc on tt.ticket = tc.ticket
    where tc.ticket in
        (select ticket from ticket_custom where name='opportunity_id' and value in ('ABCD020003'))
    group by tt.ticket,tt.submitter;

Also I don’t see the ticket_custom used anywhere in results so probably can drop that entirely also

select tt.ticket,tt.submitter,sum(tt.seconds_worked) as hours
    from ticket_time tt
    where tt.ticket in
        (select ticket from ticket_custom where name='opportunity_id' and value in ('ABCD020003'))
    group by tt.ticket,tt.submitter;

Or just join them without the subselect.

select tt.ticket,tt.submitter,sum(tt.seconds_worked) as hours
    from ticket_time tt
    join ticket_custom tc on tt.ticket = tc.ticket and tc.name='opportunity_id' and tc.value in ('ABCD020003')
    group by tt.ticket,tt.submitter;

answered Oct 11, 2020 at 14:56

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

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

1 Comment

Velu narasimman Over a year ago

that worked! Thanks a lot! I go with your last answer - without subselect.

Collectives™ on Stack Overflow

Why SUM values are wrong in postgresql query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related