Solved

Sql Group by issues

Posted on 2013-01-29
3
199 Views
Last Modified: 2013-02-03
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)
0
Comment
Question by:spoye
  • 2
3 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38832120
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
 

Accepted Solution

by:
spoye earned 0 total points
ID: 38832166
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
 

Author Closing Comment

by:spoye
ID: 38848292
I added a having clause and got the results i needed.  Thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question