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

aka_FATCATAsked:
Who is Participating?
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
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
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.