Please help before I break something...

[Parabola]

Ok... I am starting to hate Microsoft SQL....

There's that stupid rule about any columns selected MUST be contained in either an aggregate function or the group by clause. WTF.
I need help, because I don't know a harmless aggregate function for a string (varchar) and if I put these columns in the group by, of course it's going to f*** the table up...
Anyways, here's the query, someone PLEASE help me. WingedPanther, you got any ideas?

SELECT     t5.PartNumber, t2.shipquantity, t3.Description, t3.Unit, t4.Vendor, t4.VendorPartNumber, t4.ReplacementCost, t4.LastCost, t5.PageNumber
FROM         (SELECT     TOP (100) PERCENT PartNumber, SUM(ShipQuantity) AS shipquantity
                       FROM          invoices_salesline
                       GROUP BY PartNumber
                       ORDER BY ShipQuantity DESC) AS t2 INNER JOIN
                      inventory_pricing AS t3 ON t2.PartNumber = t3.PartNumber INNER JOIN
                          (SELECT     PartNumber, VendorCode, Vendor, VendorPartNumber, ReplacementCost, LastCost
                            FROM          inventory_vendor
                            WHERE      (VendorCode = 'A')) AS t4 ON t2.PartNumber = t4.PartNumber INNER JOIN
                      inventory_catalogpage AS t5 ON t2.PartNumber = t5.PartNumber
GROUP BY t2.PartNumber
ORDER BY t2.shipquantity DESC

And why the hell does it keep inserting that stupid TOP(100)PERCENT ??!?

[WingedPanther]

I haven't tested this, but here's my take on it:

SELECT     
  inventory_catalogpage.PartNumber, 
  sum(invoices_salesline.shipquantity), 
  inventory_pricing.Description, 
  inventory_pricing.Unit, 
  inventory_vendor.Vendor, 
  inventory_vendor.VendorPartNumber, 
  inventory_vendor.ReplacementCost, 
  inventory_vendor.LastCost, 
  inventory_catalogpage.PageNumber
FROM          invoices_salesline
INNER JOIN inventory_pricing ON invoices_salesline.PartNumber = inventory_pricing.PartNumber 
INNER JOIN inventory_vendor ON invoices_salesline.PartNumber = inventory_vendor.PartNumber 
INNER JOIN inventory_catalogpage ON invoices_salesline.PartNumber = inventory_catalogpage.PartNumber
WHERE      (inventory_vendor.VendorCode = 'A')
GROUP BY 
  inventory_catalogpage.PartNumber, 
  inventory_pricing.Description, 
  inventory_pricing.Unit, 
  inventory_vendor.Vendor, 
  inventory_vendor.VendorPartNumber, 
  inventory_vendor.ReplacementCost, 
  inventory_vendor.LastCost, 
  inventory_catalogpage.PageNumber
ORDER BY invoices_salesline.shipquantity DESC

In case you can't tell, I don't like subselects. If you really need them, I'd create a view instead.

[Parabola]

returned 1,135,187 rows... lol Also took well over a minute. But still, that's the problem... if I put everything in the group by, then it returns a row for each variation of each part number..... Loading a couple example resultsets for you right now.

This Table shows using everything in the GROUP BY clause in SQL:


This is an example from SQL, very stripped down though since I can't add the extra fields because of this stupid group by thing, but still gives an idea of what I need it to be:

In the second table, it was Ok to use everything in the group by (except shipquantity which WAS contained in an aggregate function) because they don't vary at all for each part.

[Parabola]

Ok, got some good progress. WithOUT the page number, all fields should be the same (except for strange exceptions), so this query works:

SELECT     TOP (250) l.PartNumber, SUM(l.ShipQuantity) AS shipquantity, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, ROUND(v.ReplacementCost, 2) 
                      AS ReplacementCost, ROUND(v.LastCost, 2) AS LastCost
FROM         invoices_salesline AS l INNER JOIN
                          (SELECT     Vendor, VendorPartNumber, PartNumber, ReplacementCost, LastCost
                            FROM          inventory_vendor
                            WHERE      (VendorCode = 'A')
                            GROUP BY PartNumber, Vendor, VendorPartNumber, ReplacementCost, LastCost) AS v ON l.PartNumber = v.PartNumber
GROUP BY l.PartNumber, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, v.ReplacementCost, v.LastCost
ORDER BY shipquantity DESC

And, I talked to my manager about it, we are only going to use the page number from the NEWEST publication that has said part. That should eliminate that issue...

EDIT: ROFL.... didn't work... even in a single publication of a catalog, a part number is on multiple pages (versions of a part)

[Parabola]

Done....

SELECT     TOP (250) l.PartNumber, SUM(l.ShipQuantity) AS shipquantity, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, ROUND(v.ReplacementCost, 2) 
                      AS ReplacementCost, ROUND(v.LastCost, 2) AS LastCost, C.PageNumber
FROM         invoices_salesline AS l INNER JOIN
                          (SELECT     Vendor, VendorPartNumber, PartNumber, ReplacementCost, LastCost
                            FROM          inventory_vendor
                            WHERE      (VendorCode = 'A')
                            GROUP BY PartNumber, Vendor, VendorPartNumber, ReplacementCost, LastCost) AS v ON l.PartNumber = v.PartNumber INNER JOIN
                          (SELECT     PartNumber, MAX(PageNumber) AS PageNumber
                            FROM          inventory_catalogpage
                            WHERE      (CatalogName = 'C08')
                            GROUP BY PartNumber) AS C ON l.PartNumber = C.PartNumber
GROUP BY l.PartNumber, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, v.ReplacementCost, v.LastCost, C.PageNumber
ORDER BY shipquantity DESC

[Parabola]

Final Edit of Query:

SELECT     TOP (250) l.PartNumber, SUM(l.ShipQuantity) AS shipquantity, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, ROUND(v.ReplacementCost, 2) 
                      AS ReplacementCost, ROUND(v.LastCost, 2) AS LastCost, C.PageNumber
FROM         invoices_salesline AS l INNER JOIN
                          (SELECT     Vendor, VendorPartNumber, PartNumber, ReplacementCost, LastCost
                            FROM          inventory_vendor
                            WHERE      (VendorCode = 'A')
                            GROUP BY PartNumber, Vendor, VendorPartNumber, ReplacementCost, LastCost) AS v ON l.PartNumber = v.PartNumber INNER JOIN
                          (SELECT     TOP (100) PERCENT PartNumber, MAX(CatalogName) AS MCatalogName, MIN(PageNumber) AS PageNumber
                            FROM          inventory_catalogpage
                            WHERE      (CatalogName < 'C90')
                            GROUP BY PartNumber
                            ORDER BY PartNumber) AS C ON l.PartNumber = C.PartNumber
GROUP BY l.PartNumber, l.Description, l.Unit, v.Vendor, v.VendorPartNumber, v.ReplacementCost, v.LastCost, C.PageNumber
ORDER BY shipquantity DESC