CODEWITHSUNDEEP
mysqlgroup-by
mmdel
mmdel

Reputation: 1279

correct use of Group By function

i am running a query to find out the slow moving stocks in my database but it's giving an error that says invalid use of group by condition. the logic is i want to find out items whose past 120 days total sales is less than 5% of that item's total stock in hand as on date. following is the query:-

SELECT 
  itemmaster.item as item, 
  stockbalance.qty as total_stock, 
  SUM(saleitems.saleqty) as total_sales 
FROM itemmaster 
JOIN stockbalance ON stockbalance.item = itemmaster.item 
JOIN saleitems ON saleitems.item = itemmaster.item 
WHERE saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW() 
  AND (SUM(saleitems.saleqty)) < (stockbalance.qty * 0.05)

Upvotes: 1

Views: 56

Answers (2)

Stephan
Stephan

Reputation: 8090

You need to move the sum condition from WHERE clause to HAVING clause.Try this:

SELECT 
  itemmaster.item as item, 
  SUM(stockbalance.qty) as total_stock, 
  SUM(saleitems.saleqty) as total_sales 
FROM itemmaster 
JOIN stockbalance ON stockbalance.item = itemmaster.item 
JOIN saleitems ON saleitems.item = itemmaster.item 
WHERE 
    saleitems.saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) AND NOW()   
GROUP BY
    itemmaster.item
HAVING
    total_sales < (total_stock * 0.05)

Upvotes: 3

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this instead:

SELECT * 
FROM
(
    SELECT 
      itemmaster.item as item, 
      stockbalance.qty as total_stock, 
      SUM(saleitems.saleqty) as total_sales 
    FROM itemmaster 
    JOIN stockbalance ON stockbalance.item = itemmaster.item 
    JOIN saleitems    ON saleitems.item    = itemmaster.item
    GROUP BY itemmaster.item, stockbalance.qty
) AS t
WHERE saledate BETWEEN DATE_SUB(NOW(),INTERVAL 120 DAY) 
                   AND NOW()
  AND total_sales < (total_stock * 0.05)

Upvotes: 3

Related Questions