postgresql - SQL Group By Issue with same item ID -

postgresql - SQL Group By Issue with same item ID -

i trying track total number of sales rep has along amount of time clocked work.

i have next 2 tables:

table1:

employeeid | item | cost | timeid ---------------------------------------- 1 | 1 | 12.92 | 123 1 | 2 | 10.00 | 123 1 | 2 | 10.00 | 456

table2:

id | minutes_in_shift -------------------------- 123 | 45 456 | 15

i bring together these 2 queries next sql:

select t1.employeeid, t1.item, t1.price, t1.shiftid, t2.minutes_in_shift table1 t1 bring together table 2 t2 on (t2.id = t1.timeid)

which homecoming next table:

employeeid | item | cost | timeid | minutes_in_shift --------------------------------------------------- 1 | 1 | 12.92 | 123 | 45 1 | 2 | 10.00 | 123 | 45 1 | 2 | 10.00 | 456 | 15

i consolidate results, however, have outcome:

employeeid | itemssold | pricetotals | totaltimeworked ----------------------------------------------------------------- 1 | 3 | 32.92 | 60

i utilize count , sum items , cost cannot figure out how show total time worked in manner appears above. note: having problem calculating time worked. in shift 123 - employee 1 working 45 minutes, regardless of how many items sold.

any suggestions?

if wish utilize sample info need extract shifts , sum minutes, this:

with ( select employeeid, count(*) itemssold, sum(price) pricetotals sampletable1 grouping employeeid), b ( select employeeid, shiftid, max(minutes_in_shift) minutes_in_shift sampletable1 grouping employeeid, shiftid), c ( select employeeid, sum(minutes_in_shift) totaltimeworked b grouping employeeid) select a.employeeid, a.itemssold, a.pricetotals, c.totaltimeworked inner bring together c on a.employeeid = c.employeeid

however, existing tables select statement much easier:

with ( select employeeid, timeid, count(*) itemssold, sum(price) pricetotals table1 grouping employeeid, timeid) select a.employeeid, sum(a.itemssold), sum(a.pricetotals), sum(table2.minutes_in_shift) totaltimeworked inner bring together table2 on a.timeid = table2.id grouping a.employeeid

sql postgresql