CODEWITHSUNDEEP
sqlmysql
Bharanikumar
Bharanikumar

Reputation: 25733

mysql group by date with multiple join

SELECT 
    tba.UpdatedDate AS  UpdatedDate,
    tsh.SupplierID,
    ts.ProductCode  as ProductCode,
    sum(tba.AfterDiscount) as AfterDiscount,
    sum(tba.Quantity) as Quantity 
FROM 
    tblstockhistory as tsh
    left join tblstock as ts 
    on tsh.StockID=ts.StockID 
    left join tblbasket as tba 
    on ts.ProductCode=tba.ProductCode 
        and tsh.SupplierID=49 
        AND tba.Status=3

group by 
    tba.UpdatedDate 
ORDER BY
    Quantity DESC

i have the supplier table, the supplier id tagged in to tblstockhistory table, and in this tblstockhistory table contains the StockID(reference from tblstock table), and i have Stock table contains StockID, ProductCode , And i have the tblbasket table , in this am maintaining the ProductCode,

My idea here , i want to show thw stats by supplierID, when i pass the supplier id, it show show , this supplier supplied goods sale stats,

But the above query sometime return null value, and it takes too much time for excution, around 50 seconds ,

I what somthing like below from above query

Date         SupplierID, Amount,  Quantity
2010-12-12      12      12200       20
2010-12-12      40      10252       30
2010-12-12      10      12551       50


2010-12-13      22      1900        20
2010-12-13      40      18652       30
2010-12-13      85      19681       50

2010-12-15      22      1900        20
2010-12-15      40      18652       30
2010-12-15      85      19681       50

Upvotes: 1

Views: 309

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

Does a tblstockhistory ever exist without a stockID. If it doesn't you can convert it to an inner join which can help.

e.g.

tblstockhistory as tsh
INNER join tblstock as ts 
on tsh.StockID=ts.StockID

Also you might to consider adding indexes if they don't currently exist.

At the very least I would have the following fields indexed since they will likely be joined and queried commonly.

  • tblstockhistory.SockID
  • tblstockhistory.SupplierID
  • tblstock.StockID
  • tblstock.ProductCode
  • tblbasket.ProductCode
  • tblBacket.Status
  • tblbasket.UpdatedDate

Finally if its really important that this query be lightening fast you can create summary tables and update them periodically.

Upvotes: 1

wizzardz
wizzardz

Reputation: 5874

re write the group by clause as and try again

group by 
    tba.UpdatedDate, tsh.SupplierID

you have mentioned ProductCode in your query but not in the 'result' you wanted if you want to display ProductCode as well then add it to the group by clause or else remove it from the select clause.

Upvotes: 0

Related Questions