Sql Group by issues

I want the output per item sum by the qty ordered.  Group by i guess i don't unerstand.  I don't want to create a table and sum  from that.   Is there a way to do this?




            select      c.item,
                        i.description,
                        i.u_m,
                        i.stocked,
                        i.safety_stock_percent,
                        w.qty_reorder,
                        i.plan_code,
                        datepart(mm,c.due_date) AS 'Due_month',
                        sum(c.qty_ordered)
            from   coitem c
                  inner join item i on i.item = c.item
                  inner join itemwhse w on w.item = i.item
            where c.due_date between @sdate and @edate
            and  i.item = 'F2002331'
            GROUP BY datepart(mm,c.due_date),
                     (qty_shipped),
                        c.item,
                        i.description,
                        i.u_m,
                        i.stocked,
                        i.safety_stock_percent,
                        w.qty_reorder,
                        i.plan_code
            order by datepart(mm,c.due_date)
stevendeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
stevenConnect With a Mentor developerAuthor Commented:
Thank you for the reply,

I added a having clause and got the results i needed.


            select      c.item,
                        i.description,
                        i.u_m,
                        i.stocked,
                        i.safety_stock_percent,
                        w.qty_reorder,
                        i.plan_code,
                        datepart(mm,c.due_date) AS 'Due_month',
                        sum(c.qty_ordered)
            from   coitem c
                  inner join co on co.co_num = c.co_num
                  inner join item i on i.item = c.item
                  inner join itemwhse w on w.item = i.item
            where c.due_date between @sdate and @edate
            and  i.item = 'F2002331'
            and  co.type <> 'e'
            GROUP BY datepart(mm,c.due_date),
            
                     (qty_reorder),
                        c.item,
                        i.description,
                        i.u_m,
                        i.stocked,
                        i.safety_stock_percent,
                        w.qty_reorder,
                        i.plan_code
            having datepart(mm,c.due_date) between 1 and 12
            
            order by datepart(mm,c.due_date)


results:

F2002331      CS-1      EA      0      0.0      0.00000000      BWS      1      3270.00000000

F2002331      CS-1      EA      0      0.0      0.00000000      BWS      2      2215.00000000

F2002331      CS-1      EA      0      0.0      0.00000000      BWS      3      4370.00000000
0
 
Ryan McCauleyData and Analytics ManagerCommented:
I'm not sure exactly what result you want to see - you say "output per item sum by the qty ordered", but I'm not sure what that would look like. Are you trying to view all the order you have and see how many items have been ordered and shipped for each order? Or are you looking for something else?

Some more detail about what you're looking to see in the end result would be helpful here (including, if possible, an example of your source data and what you actual expect as the result).
0
 
stevendeveloperAuthor Commented:
I added a having clause and got the results i needed.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.