不同的值作为新列计数 [英] distinct values as new columns & count

问题描述

我正在尝试使用 SQLite 从表中生成摘要，如下所示.

I'm trying to generate a summary from a table using SQLite as below.

我需要汇总 1) 每个模型被驾驶的次数，2) 驾驶的总距离 &3) 为驱动程序 col & 获取不同的值计算每个司机驾驶特定模型的次数 - GROUP BY modelwith COUNT(model) &SUM(distance) 将有助于 1 &2 - `我需要最后一部分 #3 的帮助，找到列的每个不同值的出现次数并将它们添加为每个模型的新列的正确方法是什么?

I need to aggregate 1) number of times each model was driven, 2) total distance driven & 3) get distinct values for driver col & count the number of times each driver has driven the particular model - GROUP BY modelwith COUNT(model) & SUM(distance) will help with 1 & 2 - `I need help with the last part #3 , what is the right approach to find number of occurrences for each distinct values of a column and add them as new columns for each model ?

id  model  datetime     driver   distance
---|-----|------------|--------|---------
1  | S   | 04/03/2009 | john   | 399 
2  | X   | 04/03/2009 | juliet | 244
3  | 3   | 04/03/2009 | borat  | 555
4  | 3   | 03/03/2009 | john   | 300
5  | X   | 03/03/2009 | juliet | 200
6  | X   | 03/03/2009 | borat  | 500
7  | S   | 24/12/2008 | borat  | 600
8  | X   | 01/01/2009 | borat  | 700

结果是

id  model| drives   distance  john   juliet  borat
---|-----|--------|---------|------|------ |------
1  | S   | 2      | 999     | 1    |   0   |  1
2  | X   | 4      | 1644    | 0    |   2   |  2
3  | 3   | 2      | 855     | 1    |   0   |  1

推荐答案

好的...这次我明白了！

OK... this time I got it!

select new_table.model, count (new_table.model) as drives, sum (new_table.distance) as distance, 
       sum(case when driver = 'john' then 1 else 0 end) as john,
       sum(case when driver = 'juliet' then 1 else 0 end) as juliet,
       sum(case when driver = 'borat' then 1 else 0 end) as borat
from new_table
group by model

这篇关于不同的值作为新列计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！