?
Solved

Sql Group by issues

Posted on 2013-01-29
3
Medium Priority
?
204 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:steven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:
steven 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:steven
ID: 38848292
I added a having clause and got the results i needed.  Thanks
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

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