none
how to get required resullt with mysql query RRS feed

  • Question

  • Hi experts , I have mysql query given below. I which counter has been used. if i enter category Id 1 for 3 times then counter is coming 3 which is correct but with this i want if i do not enter then different coloumn should come with NO.
    output should be
    <code>
        KU    Electrical    
            
    Yes    6    2    
    No    1    2    

    </code> In this KU and Electrical are my sale channel name. Yes means counter of enteries of KU  and No means which have not entered. Please help out in this. i am struggling
    <code>
    `select SalesChannel.name , Transaction.category_id, count(Transaction.category_id) as count from outlets Outlet inner join transactions Transaction on Outlet.id = Transaction.outlet_id inner join sale_channels SalesChannel on SalesChannel.id = Outlet.sale_channel_id group by Transaction.category_id`</code>

    below are three tables which i used
    1. transactions
    <code>

        CREATE TABLE IF NOT EXISTS `transactions` (
        `id` int(11) NOT NULL,
          `zone_id` int(11) NOT NULL,
          `state_id` int(11) NOT NULL,
          `city_id` int(11) NOT NULL,
          `category_id` int(11) NOT NULL,
          `sub_category_id` int(11) NOT NULL,
          `brand_id` int(11) NOT NULL,
          `model_id` int(11) NOT NULL,
          `outlet_id` int(11) NOT NULL,
          `no_of_units` int(11) NOT NULL,
          `mop` decimal(10,2) NOT NULL
        ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
        
        --
        -- Dumping data for table `transactions`
        --
        
        INSERT INTO `transactions` (`id`, `zone_id`, `state_id`, `city_id`, `category_id`, `sub_category_id`, `brand_id`, `model_id`, `outlet_id`, `no_of_units`, `mop`) VALUES
        (1, 2, 2, 2, 2, 1, 1, 1, 1, 3, '6.00'),
        (2, 2, 2, 2, 2, 1, 1, 1, 1, 3, '6.00'),
        (3, 1, 1, 1, 1, 1, 1, 1, 1, 4, '2.00'),
        (4, 2, 2, 2, 1, 1, 1, 1, 2, 4, '2.00');

    </code>
    2.outlets
    <code>

        CREATE TABLE IF NOT EXISTS `outlets` (
        `id` int(11) NOT NULL,
          `outlet_code` varchar(255) NOT NULL,
          `name` varchar(255) NOT NULL,
          `zone_id` int(11) NOT NULL,
          `state_id` int(11) NOT NULL,
          `city_id` int(11) NOT NULL,
          `sale_channel_id` int(11) NOT NULL,
          `is_active` tinyint(1) NOT NULL,
          `created` datetime NOT NULL,
          `modified` datetime NOT NULL
        ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
        
        --
        -- Dumping data for table `outlets`
        --
        
        INSERT INTO `outlets` (`id`, `outlet_code`, `name`, `zone_id`, `state_id`, `city_id`, `sale_channel_id`, `is_active`, `created`, `modified`) VALUES
        (1, '1508', 'Ashok electricals', 2, 2, 2, 1, 1, '2016-10-03 00:00:00', '2016-10-03 00:00:00'),
        (2, '1233', 'vinayak electricals', 1, 1, 1, 2, 1, '2016-10-04 00:00:00', '2016-10-04 00:00:00');

    </code>
    3. sale_chennals
    <code>

        CREATE TABLE IF NOT EXISTS `sale_channels` (
        `id` int(11) NOT NULL,
          `name` varchar(255) NOT NULL,
          `is_active` tinyint(1) NOT NULL,
          `created` datetime NOT NULL,
          `modified` datetime NOT NULL
        ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
        
        --
        -- Dumping data for table `sale_channels`
        --
        
        INSERT INTO `sale_channels` (`id`, `name`, `is_active`, `created`, `modified`) VALUES
        (1, 'KU', 1, '2016-10-03 00:00:00', '2016-10-03 00:00:00'),
        (2, 'Electricals', 1, '2016-10-04 00:00:00', '2016-10-04 00:00:00');

    </code>
    • Moved by litdev Wednesday, October 5, 2016 5:39 PM
    Wednesday, October 5, 2016 7:15 AM

Answers

All replies