Solved

Sql Group by issues

Posted on 2013-01-29
3
195 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
Comment Utility
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
Comment Utility
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
Comment Utility
I added a having clause and got the results i needed.  Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now