aka_FATCAT
asked on
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.
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
Last Comment
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ok. That worked great. But now how do I get it to tell me the name of the product.
I added ; i.itemname
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Is that the right way, or is it just a work around?
the fields that are aggregated should not be grouped all the other should
It is a right way
for every ii.itemNum ther must be unique item name correct
then this is right way
for every ii.itemNum ther must be unique item name correct
then this is right way
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window