SQLTeam.com | Weblogs | Forums

Group by doesnt work


#1

Group by doesnt work. I need to add time for particular user. time is the 3rd column of result set posted below. first is the quantity

SELECT
sum(woitem.qtytarget) AS woitemtotal, sysuser.username as USERNAME, timeworked.info as timetaken

FROM mo
INNER JOIN moitem ON mo.id = moitem.moid
LEFT JOIN wo ON moitem.id = wo.moitemid
LEFT JOIN woitem ON wo.id = woitem.woid AND woitem.typeid = 10
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
left join customdecimal timeworked on timeworked.recordid = mo.id
WHERE cast(wo.datestarted as date) = '10-21-2015'
group by sysuser.username

35 ACCGRADE1 3
35 ACCSHIPPING1 4
70 ACCSORT1 7
20 ACCSORT2 3.5
80 ACCSORT3 7
274.55 BATTGRADE1 1.75
1,215.35 BATTGRADE1 2
395.65 BATTGRADE1 3
746.4 BATTGRADE1 4
307.45 BATTGRADE1 5
4 BATTGRADE10 0.2
8 BATTGRADE10 0.4
21 BATTGRADE10 0.8


How can we group this query only by USER?
#2

You probably need to sum the time (timeworked.info)


#3

It gives different result. Instead of showing xyz = 2 hours, it shows xyz = 5 hours


#4

it should give a different result -- the correct one


#5

Different but it gives wrong result.


#6

then please post the source data and the result you expect. Your original query neither groups by timeworked.info nor aggregates it. You must do one or the other


#7

Individually, I get correct. Like for example,

gbritton : 0.2
gbritton : 0.8
gbritton : 1.0
when I group or sum, it should give
gbritton : 2.0 but it gives 5.0


#8

Tried grouping, but gives wrong result. How do I aggregate it? How do I give source data?


#9

post your sample data and grouped query. this works:

select nm, sum(val)
from 
(select *
from (values ('gbritton', 0.2), ('gbritton', 0.8), ('gbritton', 1.0))v(nm, val)
) _
group by nm

#10

There are many. I cant manually enter the values


#11

just a subset, please. A subset that demonstrates the problem, that is. Also, you can copy and paste them -- no need to enter by hand


#12

This is the grouped query

select distinct sysuser.username as WOUser, sum(timeworked.info) as timeworked, sum(woitem.qtytarget) AS woitemtotal

from po
inner join trackinginfo lot on lot.info = po.num and lot.tableid = -355941248
inner join woitem on lot.recordid = woitem.id
inner join wo on woitem.woid = wo.id AND woitem.typeid = 10
inner join part on woitem.partid = part.id
inner join product on product.partid = part.id
inner join moitem on wo.moitemid = moitem.id
inner join mo on moitem.moid = mo.id
left join sysuser on sysuser.id = wo.userid
left join customdecimal timeworked on timeworked.customfieldid = 15 and timeworked.recordid = mo.id
WHERE cast(wo.datestarted as date) = '2015-10-21'
group by sysuser.username , timeworked.info
order by sysuser.username

WOUSER TIMEWORKED WOITEMTOTAL
ACCGRADE1 6 35
ACCSHIPPING1 8 35
ACCSORT1 14 70
ACCSORT2 7 20
ACCSORT3 14 80
BATTGRADE1 9 395.65
BATTGRADE1 25 307.45
BATTGRADE1 28 274.55
BATTGRADE1 32 746.4
BATTGRADE1 92 1,215.35


#13

Continued here.