在同一列中使用分组依据来计算相同的ID [英] Count the same id with group by in one single column

问题描述

我有这样的桌子 kondisi

+------------+----------------+
| id_kondisi | id_sub_kondisi |
+------------+----------------+
| 01         | 0102           |
| 03         | 0302           |
| 01         | 0101           |
| 01         | 0102           |
| 01         | 0101           |
| 03         | 0301           |
| 03         | 0303           |
| 02         | 0202           |
| 01         | 0102           |
| 03         | 0301           |
| 01         | 0101           |
| 02         | 0203           |
| 03         | 0302           |
| 02         | 0202           |
| 02         | 0201           |
| 02         | 0202           |
+------------+----------------+
16 rows in set (0.00 sec)

我想要一张看起来像这样的表的结果

I want to a result of the table that looks like this

+----------------+-------------+
| kondisi_tot    | coun_tot    |
+----------------+-------------+
| 01             |  6          |
| 0101           |  3          |
| 0102           |  3          |
| 02             |  5          |
| 0201           |  1          |
| 0202           |  3          |
| 0203           |  1          |
| 03             |  5          |
| 0301           |  2          |
| 0302           |  2          |
| 0303           |  1          |
+----------------+-------------+

所以我需要计算已经循环的数据ID.就像上面的结果 我知道我必须使用group by，但是如何将另一列变成一列呢?

SO i need to count the data id that have been looping. Just like the above result i know i have to use group by but how do i make the other column into one column ?

PS:我的id_kondisi和id_sub_kondisi是char类型，而不是int类型

推荐答案

类似的东西应该可以工作.请注意，UNION ALL用于确保对所有值都进行计数.然后将ID值强制转换为CHAR以使排序顺序起作用.

Something like this should work. Note that UNION ALL is used to make sure all values are counted; the ID value is then casted to CHAR to make the sort order work.

SQL小提琴

MySQL 5.6模式设置:

CREATE TABLE kondisi
    (`id_kondisi` int, `id_sub_kondisi` int)
;

INSERT INTO kondisi
    (`id_kondisi`, `id_sub_kondisi`)
VALUES
    (01, 0102),
    (03, 0302),
    (01, 0101),
    (01, 0102),
    (01, 0101),
    (03, 0301),
    (03, 0303),
    (02, 0202),
    (01, 0102),
    (03, 0301),
    (01, 0101),
    (02, 0203),
    (03, 0302),
    (02, 0202),
    (02, 0201),
    (02, 0202)
;

查询1 :

select id, count(id) 
from 
(select id_kondisi as id from kondisi
union all
select id_sub_kondisi from kondisi) merged_table
group by id
order by cast(id as char) 

结果 :

Results:

    |  id | count(id) |
    |-----|-----------|
    |   1 |         6 |
    | 101 |         3 |
    | 102 |         3 |
    |   2 |         5 |
    | 201 |         1 |
    | 202 |         3 |
    | 203 |         1 |
    |   3 |         5 |
    | 301 |         2 |
    | 302 |         2 |
    | 303 |         1 |

这篇关于在同一列中使用分组依据来计算相同的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！