Group by doesnt work
-
Below code works fine if I add timeworked.info in the group by. It doesn't worked without it. Basically below code gives the quantity, user and time worked. Since same user is working on several quantities, I want to group them adding the time they worked. But it doesn't work that way.:hehe:
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
-
chindilog (10/22/2015)
Below code works fine if I add timeworked.info in the group by. It doesn't worked without it. Basically below code gives the quantity, user and time worked. Since same user is working on several quantities, I want to group them adding the time they worked. But it doesn't work that way.:hehe: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
If you want help we will need the DDL for the tables, sample data (not production data), and the expected results based on the sample data.
For help with this please read the first article I have referenced below in my signature block.
-
You need to either include it in the GROUP BY or use it in an aggregate function. Otherwise, there's no logic to which value should be shown.
-
since there's more than one row, and you only want to group on one column, you need an aggregate function;
any of these would be my first examples to see what is appropriate:
MIN(timeworked.info) as timetaken
MAX(timeworked.info) as timetaken
SUM(timeworked.info) as timetaken
COUNT(timeworked.info) as timetaken
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
It doesnt work that way. I get wrong info if I sum up the time.
-
DDL, sample data and expected results then?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (10/22/2015)
DDL, sample data and expected results then?
Lowell, you reminded me of one of the most honest sign I saw a homeless person holding.
It said: "NEED BEER!"
😀
Alvin Ramard
Memphis PASS Chapter[/url]All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply