Subselecting a SUM on a matching value

[duckstar]

Hi Guys,

I have quite a big problem with a statement.  Hopefully someone will be able to help me !

Its run on a very large database.

SELECT s.Branch, s.Supplier, s.AdviceNoteNumber, COUNT(s.ProductCode) AS theCount, SUM(s.Quantity) AS totalQuantity, SUM(s.GoodsTotal) AS GoodsTotal, s.OrderNumber,
DATE_FORMAT(s.DateCreated,'%d/%m/%Y')AS cDate, s.DateCreated as SortDate, p.Name,
p.CurrentID, m.AdviceNote, DATE_FORMAT(m.MatchedDate,'%d/%m/%Y')AS mDate, m.ID AS mID,
m.MatchedInvoiceNumber, m.MatchedInvoiceValue, m.TotalQuantity as MatchQty, m.MatchedSecInvoiceNumber ,
m.MatchedSecInvoiceValue 
FROM stockmovementhistory s 
INNER JOIN salespersons p ON p.Branch = s.Branch AND p.ID = s.SalesPersonID 
INNER JOIN Suppliers sp ON sp.Branch = s.Branch AND sp.ShortName = s.Supplier 
LEFT JOIN invoicechecking.MatchedHistory m ON m.Branch = s.Branch AND m.AdviceNote = s.AdviceNoteNumber AND m.Supplier = s.Supplier 
WHERE s.DateCreated >'2010-03-01' AND s.DateCreated <'2010-04-01' AND s.Branch ='BH' AND s.RecordTypeID = '2' AND sp.InterBranch = '0' AND (s.AdviceNoteNumber LIKE 'mk%' OR s.Supplier LIKE 'mk%') GROUP BY s.Branch, s.AdviceNoteNumber, s.Supplier ORDER BY SortDate

 

This is the statement that works fine and dandy.

I now want to instead of counting "theCount" which counts all the productcode's.  I want it to only count the product codes that aren't in another table.

My other table is called RebateProducts and contains ProductCode and Cost.

I have tried loads of things, including whacking this in there :

(SELECT SUM(rp.ProductCode) FROM invoicechecking.RebateProducts rp WHERE rp.ProductCode = s.ProductCode) as RBTheCount

To try and count the number of ProductCode's that are in the list so that i can deduct it later.

 

Thanks in advance !

 

 

[duckstar]

My head is going to explode in a minute.

I will try and describe exactly what i am trying to do...

 

I want a count of all the products from each group (group on the advicenote and supplier fields)

But i want a count of the products that are not in the RebateProducts table and i want a count that are in the table.

I then want the total cost its pulling up from the history for the items that are not in the RebateProducts table.

then i want the total cost of the products that are.

 

I.e. it will find a product from the stockmovementhistory table and it is in the rebate table.

So it will take the s.Quantity of that product and times it against the rebateproduct table cost.  Rather than using the stockmovementhistory cost....

 

Something like this, but obviously this isn't correct as you cannot have a WHERE without a SELECT and FROM

COUNT(s.ProductCode WHERE IN(SELECT ProductCode FROM RebateProducts) as RBTheCount

 

[fenway]

TLDR.