SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: some SQL help

  1. #1
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    some SQL help

    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
    Code:
    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?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Could you post the create table data for each table.

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Could you post the create table data for each table.
    is there a quick way to do that in phpmyadmin?

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doh, here it is:

    Code:
    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 ;

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Code SQL:
    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.

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    where t1 = table names yeah?

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    t1 is an alias of the derived table. t2 is a alias of the products table.

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm confused

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    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.

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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?

  11. #11
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    You should be able to copy that query into phpmyadmin and run it just fine.

  12. #12
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    returned empty result set

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    I was saying if you would like to select other columns:

    Code SQL:
    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

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Does the order details table have any orders?

    Otherwise, try running the subquery separately.

    Code SQL:
    (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)

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    Glasgow
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •