sum of sales of all products in a category

my query is giving me wrong date order

[phparion]

my query works with 4 tables

products, sales, sale_items, categories

I am trying to fetch sales of last 6 months, month-wise, of all the products of a given category.
the query shows me correct sum of sales quantities but the problem is that

1) I do not get a 0 when there is no sales of a product in a month and
2) the second issue is wrong order of date.

My desired date order is

Oct-2022, Nov-2022, Dec-2022, Jan-2023, Feb-2023, March-2023

But I am getting it Jan-23, Feb-2023, March-2023, Sep-2022, Oct-2022, Nov-2022, Dec-2022

SELECT DATE_FORMAT(s.date,'%b-%y') as mdate,
     DATE_FORMAT(s.date,'%m') as omdate,
     DATE_FORMAT(s.date,'%y') as oydate,
     

  si.product_name, 
  SUM(FLOOR(si.quantity)) as quantity
  
FROM sale_items si, 
         sales s, 
         products p, 
         categories c    
     
WHERE s.warehouse_id = si.warehouse_id 
AND s.id = si.sale_id
AND s.warehouse_id BETWEEN 1 AND 50
AND p.category_id = c.id
   AND si.product_id = p.id
   AND c.id = 4

  AND s.date >= CURDATE() - INTERVAL 6 MONTH
   
   GROUP BY si.product_id,mdate
   
  ORDER BY si.product_name, omdate,oydate DESC

Any help is much appreciated

[topr8]

have you tried something like

ORDER BY si.product_name DESC, oydate ASC, omdate DESC

[topr8]

for (1) you need to use a left outer join instead of an inner join

[phparion]

 ORDER BY si.product_name DESC, oydate ASC, omdate DESC 

it gives correct date order but mixes up products order-- I want the same product to appear together for example

product A, Feb-23, 200
product A, Jan-23, 100
product A, Dec-22, 300
product B, Feb-23, 80
....

but with your suggested ORDER BY same product is not displayed together. they are all mixed up as follow

product A, Feb-23, 200
product M, Feb-23, 100
product Z, Feb-23, 300
product D, Feb-23, 80

[LifeinAsia]

Try:

ORDER BY si.product_name DESC, oydate, omdate

[phparion]

hi,

now there is a scenario where some products exist under a category but they are not sold in the last one month so this following query is not picking them may be because sales table does not match a record depending on dates.

I tried to use a LEFT JOIN of categories on products table but may be because of multiple tables i am not getting a desired results. can anybody help please how to get ALL PRODUCTS even if they are not sold in the given dates, i can show a 0 in sold qty.

thank you

SELECT DATE_FORMAT(s.date,'%b-%y') as mdate,
   DATE_FORMAT(s.date,'%m') as omdate,
   DATE_FORMAT(s.date,'%y') as oydate,
   

 si.product_name, 
 SUM(FLOOR(si.quantity)) as quantity
 
FROM sale_items si, 
     sales s, 
     products p, 
     categories c  
   
WHERE s.warehouse_id = si.warehouse_id 
AND s.id = si.sale_id
AND s.warehouse_id BETWEEN 1 AND 50
AND p.category_id = c.id
  AND si.product_id = p.id
  AND c.id = 4

 AND s.date >= CURDATE() - INTERVAL 6 MONTH
  
  GROUP BY si.product_id,mdate
  
 ORDER BY si.product_name, omdate,oydate DESC

[phparion]

I got it working, posting it just in case somebody else is looking for the same results,

SELECT p.id, p.name, p.code, IFNULL(si.qty,0) as qty
  FROM products p
  LEFT JOIN (
  SELECT SUM(FLOOR(ssi.quantity)) as qty, ssi.product_id
  FROM sale_items ssi, sales ss
  WHERE ss.id = ssi.sale_id
  AND
  ss.date >= CURDATE() - INTERVAL 1 MONTH
  GROUP BY ssi.id
  ) as si

  ON p.id = si.product_id
  WHERE p.category_id = " . $catid . "
  GROUP BY p.id