mysql - 在 mysql 查询中显示计数器

Question

我有下面给出的 mysql 查询。我已经使用了哪个计数器。如果我输入类别 ID 1 3 次，那么计数器就会出现 3，这是正确的，但是我想如果我不输入，那么不同的列应该带有 NO。输出应该是

      KU      Electrical  Yes   6       2  No    1       2  

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

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;

下面是我使用的三个表格

1) 交易

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');

2) 网点

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');

3) sale_chennals

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');

SQL fiddle :http://sqlfiddle.com/#!9/3f497/1

Answer 1

您正在按类别分组。这意味着每个类别您都会获得一个结果行。在每一行中，您都会显示计数和销售 channel 名称。该销售 channel 名称只是在任意选择的类别记录中找到的名称之一。

我想您想按类别和销售 channel 进行计数。因此，您的 group by 子句应该是 group by SalesChannel.name, Transaction.category_id:

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 SalesChannel.name, Transaction.category_id;

SQL fiddle :http://sqlfiddle.com/#!9/3f497/2

但是，此结果不显示电气/类别 2 的条目，因为表中没有此组合的事务。如果您想为此显示零计数，则必须首先创建完整的结果集(即 channel 和类别的所有组合，无论它们是否有交易)。然后您将外部加入交易:

select 
  sc.name, 
  c.id as category_id, 
  count(t.id) as "count"
from sale_channels sc
cross join categories c
left join outlets o on o.sale_channel_id = sc.id
left join transactions t on t.outlet_id = o.id and t.category_id = c.id
group by sc.name, c.id;

SQL fiddle :http://sqlfiddle.com/#!9/60e998/5