CODEWITHSUNDEEP
mysqlmagentoproduct
user4476538
user4476538

Reputation: 1

Least selling products

I have been searching for the query over the internet for the past few days regarding the "Least selling products" in Magento or a list of products that are not sold or at least have the fewest quantity sold and i have not been that lucky enough to find one. My first attempt was just to reverse the query from best selling products (sales_flat_order_item) but the problem is that I would only be getting a list products that are sold with the least quantity not the products which were not sold or 0 quantity sold. Can anyone at least give me some an idea or tips on how to start this one? Thank you in advance.

Upvotes: 0

Views: 1244

Answers (1)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

Assuming your table structure is somewhat similar to below :

CREATE TABLE products
    (`id` int, `name` varchar(1))
;

INSERT INTO products
    (`id`, `name`)
VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c'),
    (4, 'd')
;

CREATE TABLE sales
    (`saleid` int, `prodid` int)
;

INSERT INTO sales
    (`saleid`, `prodid`)
VALUES
    (1, 1),
    (2, 1),
    (3, 2),
    (4, 3),
    (5, 1),
    (6, 2)
;

This query will fetch the required data for you.

select p.name, count(s.saleid) as sale from 
products p
left join sales s on p.id = s.prodid group by p.name order by sale

data: 

NAME    SALE
d   0
c   1
b   2
a   3

SQLFIDDLE

Upvotes: 1

Related Questions