SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need report like below

    Hi,

    i have table like this,

    |-------------------------------------------------------------------|

    CREATE TABLE `products` (
    `id` int(11) NOT NULL auto_increment,
    `merchant` varchar(255) NOT NULL default '',
    `name` varchar(255) NOT NULL default '',
    `description` text NOT NULL,
    `image_url` varchar(255) NOT NULL default '',
    `buy_url` text NOT NULL,
    `price` decimal(10,2) NOT NULL default '0.00',
    `search_name` varchar(255) NOT NULL default '',
    `category` varchar(255) NOT NULL default '',
    `brand` varchar(255) NOT NULL default '',
    `dupe_hash` varchar(32) NOT NULL default '',
    `rating` int(11) NOT NULL default '0',
    `reviews` int(11) NOT NULL default '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `dupe_filter` (`dupe_hash`),
    KEY `merchant` (`merchant`),
    KEY `name` (`name`),
    KEY `search_name` (`search_name`),
    KEY `category` (`category`),
    KEY `brand` (`brand`),
    FULLTEXT KEY `name_2` (`name`)
    ) ENGINE=MyISAM AUTO_INCREMENT=32185 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32185 ;



    |-------------------------------------------------------------------|

    i need to display report as follows,( i need first sort by merchant & second brand)

    Merchant | Brand | No. Of products( related to merchant & brand)

    please reply soon,

    Thanks in Advance.

    Sunil.

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,,
    i kept the query like this is it ok?

    SELECT merchant, brand, count(*) no_of_products FROM `comp_products` where brand!='' group by merchant,brand order by merchant, brand

    please confirm me.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    It looks ok to me. But why asking, try it. Then, if it doesn't work or gives the wrong info and you don't understand why, come here and ask.

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    actually my doubt is, can i use two columns in one 'Group By'.

    Regards,
    Sunil.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    yes

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when in doubt, consult the manual. and again, with a SELECT, try it and see. An INSERT, UPDATE or DELETE you would want to try on a test table of course.


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
  •