How can I count the number of occurrences of a range of values in one column, against all unique values in another, in MySQL?

问题: I have a table with any number of duplicates in the brand column, with a limited number of options in the size column: Item Brand Size 1 BiGNRG...

问题:

I have a table with any number of duplicates in the brand column, with a limited number of options in the size column:

Item    Brand           Size
1       BiGNRG          AA
2       LongLife        AAA
3       LongLife        AAA
4       BiGNRG          AA
5       LongLife        D
6       EcoBatt         C
7       BiGNRG          AAA
8       BiGNRG          AA
9       EcoBatt         C

I need an SQL query to convert that information to this:

Brand    AA     AAA     C       D
BiGNRG   3      1       0       0
EcoBatt  0      0       2       0
LongLife 0      2       0       1

I cannot figure out how to convert this data into this format.


回答1:

if the number of the option is limited you could try using an group by un select union all

select brand, count(AA) AA, count(AAA)  AAA, count(C) C, count(D) D 
from (
    select  Brand, 'AA' AA ,  NULL  AAA,  NULL C , NULL D 
    from my_table 
    where size ='AA'
    union ALL 
    select  Brand, null ,  'AAA'  ,  NULL , NULL
    from my_table 
    where size ='AAA'
    union ALL 
    select  Brand, null ,  null   ,  'C' , NULL
    from my_table 
    where size ='C'
    union ALL 
    select  Brand, null ,  null   , null,  'D'
    from my_table 
    where size ='D'
) T 
group by brand 
  • 发表于 2019-01-22 22:03
  • 阅读 ( 191 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除