mysql - 更新内部查询结果

Question

我有一个查询和结果如下。
在数据库中NULL和0代表的意思是一样的。
现在我想要一个基于 Null+0 或 1 的计数器

例如:在下面的示例中我想要这样的结果:

IsVirtual   Category    counter
NULL+0      3           343+8 = (351 is Total)

查询

select * from 
(
    Select IsVirtual, Category, count(*) as counter
    from [Hardware]
    group by IsVirtual, Category
) innercat

输出

+-----------+----------+---------+
| IsVirtual | Category | counter |
+-----------+----------+---------+
| NULL      |  3       |    343  |
| 0         |  3       |    8    |
| 1         |  2       |    1    |
| 0         |  1       |    1    |
| NULL      |  6       |    119  |
| 0         |  4       |    1    |
| NULL      |  1       |    70   |
| 0         |  5       |    9    |
| NULL      |  4       |    54   |
| 0         |  2       |    2    |
| NULL      |  5       |    41   |
| NULL      |  2       |    112  |
| 1         |  1       |    5    |
+-----------+----------+---------+

Answer 1

我想你想要这个:

SELECT COALESCE(IsVirtual, 0) as [IsVirtual],
       Category,
       Count(*) as [Counter]
FROM   yourtable
GROUP  BY COALESCE(IsVirtual, 0),Category  

这将在不使用子查询的情况下为您提供预期结果。