I use postgresql and I have trouble in forming the correct query to get the desired result. I have a query And it gives me result as below. I wish to get the result like So I tried to SUM the third column and so changed the query a bit like this, But the result is like below, Fist this

Why SUM values are wrong in postgresql query?

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!

Answer

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;

Advertisement

Answer