some SQL help

[stevieg_83]

I'm trying to build an admin section for an e-commerce site and needing a helping hand with the SQL.

I want to display the top 10 selling products.

Obviously products are in a products table, I have an orders table which contains a summary of the order and also an ordersdetail table that holds products ordered, quantity etc.

Any ideas on where to start? I had thought along the lines of

SELECT *
FROM products,  orderdetails
WHERE products.ProdID = orderdetails.DetailProductID
ORDER BY orderdetails.DetailQuantity

but no such joy there.

should there be a COUNT or SUM statement?

[oddz]

Could you post the create table data for each table.

[stevieg_83]

Could you post the create table data for each table.

is there a quick way to do that in phpmyadmin?

[stevieg_83]

doh, here it is:

CREATE TABLE IF NOT EXISTS `orderdetails` (
  `DetailsID` int(11) NOT NULL AUTO_INCREMENT,
  `DetailOrderID` int(11) DEFAULT NULL,
  `DetailProductID` int(11) DEFAULT NULL,
  `DetailProductName` varchar(50) DEFAULT NULL,
  `DetailProductDesc` varchar(255) DEFAULT NULL,
  `DetailQuantity` int(11) DEFAULT NULL,
  `DetailPrice` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`DetailsID`),
  KEY `DetailOrderID` (`DetailOrderID`),
  KEY `DetailProductID` (`DetailProductID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `OrderID` int(10) NOT NULL AUTO_INCREMENT COMMENT 'Unique record ID - the Primary key',
  `OrderReferenceID` varchar(50) DEFAULT NULL COMMENT 'Internal Tracking number - defined as text to allow alphanumeric entry',
  `OrderUserID` int(10) DEFAULT NULL COMMENT 'Foreign key for Visitors table',
  `OrderShipping` decimal(19,2) DEFAULT NULL COMMENT 'Shipping cost for order',
  `OrderTax` decimal(19,2) DEFAULT NULL COMMENT 'Tax charge for order',
  `OrderTotal` decimal(19,2) DEFAULT NULL COMMENT 'Total for order',
  `OrderDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date when order was placed; defaults to Current_Timestamp function',
  `OrderStatus` int(11) NOT NULL,
  `OrderShipAddress1` varchar(50) DEFAULT NULL COMMENT 'First address line for shipping',
  `OrderShipAddress2` varchar(50) DEFAULT NULL COMMENT 'Second address line for shipping',
  `OrderShipCity` varchar(50) DEFAULT NULL COMMENT 'City of shipping address',
  `OrderShipStateID` int(10) DEFAULT NULL COMMENT 'Foreign key for States_Provinces table',
  `OrderShipZip` varchar(50) DEFAULT NULL COMMENT 'Zip or postal code of shipping address',
  `OrderShipCountryID` int(10) DEFAULT NULL COMMENT 'Foreign key for Countries table',
  KEY `OrderID` (`OrderID`),
  KEY `OrderReferenceID` (`OrderReferenceID`),
  KEY `OrderShipCountryID` (`OrderShipCountryID`),
  KEY `OrderUserID` (`OrderUserID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `ProdID` int(11) NOT NULL AUTO_INCREMENT,
  `ProdBrandID` int(11) DEFAULT NULL,
  `ProdName` varchar(255) DEFAULT NULL,
  `ProdShortDesc` varchar(255) DEFAULT NULL,
  `ProdLongDesc` mediumtext,
  `ProdKeywords` mediumtext,
  `ProdPrice` decimal(6,2) DEFAULT NULL,
  `ProdRRP` decimal(6,2) DEFAULT NULL,
  `ProdSKU` varchar(255) DEFAULT NULL,
  `ProdThumb` varchar(255) DEFAULT NULL,
  `ProdImage` varchar(255) DEFAULT NULL,
  `ProdImageAlt` varchar(64) DEFAULT NULL,
  `ProdCatID` int(11) DEFAULT NULL,
  `ProdIsFeatured` int(11) DEFAULT NULL,
  PRIMARY KEY (`ProdID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

[oddz]

SELECT 
     t2.ProdID AS id
	 ,t1.nums_orders AS nums_orders
  FROM
     (SELECT
           t1.DetailProductID AS product_id
           ,COUNT(t1.DetailsID) AS nums_orders
        FROM
           orderdetails AS t1
       GROUP
          BY
           t1.DetailProductID
       ORDER
          BY
           nums_orders DESC
       LIMIT
           10) AS t1
 INNER
  JOIN
      products AS t2
    ON
      t1.product_id = t2.ProdID

Any other columns that are needed can be added to the outer most select.

[stevieg_83]

where t1 = table names yeah?

[oddz]

t1 is an alias of the derived table. t2 is a alias of the products table.

[stevieg_83]

I'm confused

[oddz]

Does the query produce the expected result? You should end up with a result set with the primary key of the product and the number of orders that product has. The first row will represent the product with the most orders and the last the product with the least within the top 10.

[stevieg_83]

Does the query produce the expected results? You should end up with a result set with the primary key of the product and the number of orders that product has.

haven't tried it, wasn't sure if I had to replace t1 etc with the actual table names. You saying I should be able to run your query as is and it'll display?

[oddz]

You should be able to copy that query into phpmyadmin and run it just fine.

[stevieg_83]

returned empty result set

[oddz]

I was saying if you would like to select other columns:

SELECT 
     t2.ProdID AS id
	 ,t1.nums_orders AS nums_orders
	 ,t2.ProdPrice AS price
	 ,t2.ProdThumb AS thumb
	 ,t2.ProdImageAlt AS img_alt
	 ,t2.ProdKeywords AS keywords
  FROM
     (SELECT
           t1.DetailProductID AS product_id
           ,COUNT(t1.ProdID) AS nums_orders
        FROM
           orderdetails AS t1
       GROUP
          BY
           t1.DetailProductID
       ORDER
          BY
           nums_orders DESC
       LIMIT
           10) AS t1
 INNER
  JOIN
      products AS t2
    ON
      t1.product_id = t2.ProdID

[oddz]

Does the order details table have any orders?

Otherwise, try running the subquery separately.

(SELECT
           t1.DetailProductID AS product_id
           ,COUNT(t1.DetailsID) AS nums_orders
        FROM
           orderdetails AS t1
       GROUP
          BY
           t1.DetailProductID
       ORDER
          BY
           nums_orders DESC
       LIMIT
           10)

[stevieg_83]

yeah I've 4 records in that table, although just realised that they're not related to an order.

I'll take it through checkout again to generate some orders.