[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Group By

I know this is wrong, how do I make it right?

I need to find out, what sold, how many, at what cost, at what retail price, from a specific vendor during a specific time period.

The only unique thing should be ii.itemnum

This query works, but I know that I made a mess of it with the group by clause.

Please advise.

SELECT ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY ii.ItemNum, ii.quantity, ii.CostPer, ii.PricePer

Open in new window

0
aka_FATCAT
Asked:
aka_FATCAT
  • 3
  • 2
  • 2
2 Solutions
 
Pratima PharandeCommented:
try this

SELECT ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY ii.ItemNum
0
 
EyalCommented:
fields that are aggregated should not be grouped

SELECT ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY ii.ItemNum

Open in new window

0
 
aka_FATCATAuthor Commented:
Ok. That worked great. But now how do I get it to tell me the name of the product.

I added ; i.itemname
SELECT i.itemname, ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY ii.ItemNum

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Pratima PharandeCommented:
SELECT i.itemname, ii.itemNum, SUM(ii.Quantity) as quantity, SUM(ii.CostPer) as cost, SUM(ii.PricePer) as price
FROM invoice_itemized ii
LEFT OUTER JOIN Inventory i
ON ii.ItemNum = i.ItemNum
LEFT OUTER JOIN vendors v
ON i.vendor_number = v.vendor_number
LEFT OUTER JOIN Invoice_Totals it
ON ii.invoice_number = it.invoice_number
WHERE it.DateTime BETWEEN '09/01/2011' AND '09/30/2011'
AND v.Vendor_Number = '8185021313'
GROUP BY i.itemname, ii.ItemNum
0
 
aka_FATCATAuthor Commented:
Is that the right way, or is it just a work around?
0
 
EyalCommented:
the fields that are aggregated should not be grouped all the other should
0
 
Pratima PharandeCommented:
It is a right way

for every  ii.itemNum ther must be unique item name correct

then this is right way
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now